sql-server - 查找带有前缀和后缀的匹配号码

Question

我有下表与示例记录。

CREATE TABLE Testtbl
(
    Number1 int,
    Number2 int,
    AddressA varchar(50),
    AddressB varchar(50),
    originalNumber1 int,
    originalNumber2 int
);

记录：

INSERT INTO Testtbl VALUES(12345,9876,'A1','B1',9876,12345),
                          (4321,761,'A2','B2',924321,761110),
                          (159,9544,'A3','B3',929544,110159),
                          (158,98714,'A4','B4',110158,9298714),
                          (105,9804,'A5','B5',105110,9804110);

我想找到 columnns和Number1,的匹配。如果匹配，则显示else 值。originalNumber1Number2originalNumber2Number1originalNumber1

列originalNumber1和originalNumber2包含前缀和后缀，如记录中所示92和110。

预期输出：

Num1    Num2
-------------
9876    12345
4321    761
929544  1110159
158     98714
105     9804

我的尝试：

SELECT  CASE WHEN Number1 LIKE '%'+ CAST(OriginalNumber1 AS VARCHAR(20)) +'%' 
                THEN Number1 ELSE OriginalNumber1 END AS Num1,
        CASE WHEN Number2 LIKE '%'+ CAST(OriginalNumber2 AS VARCHAR(20)) +'%' 
                THEN Number2 ELSE OriginalNumber2 END AS Num2
FROM Testtbl

标签： sql-serversql-server-2008-r2

Answer 1

使用CASE

SELECT  CASE WHEN OriginalNumber1 LIKE '%'+ CAST(Number1 AS VARCHAR(20)) +'%' 
                   THEN Number1                 
                ELSE OriginalNumber1 END AS Num1,
        CASE WHEN  OriginalNumber2 LIKE '%'+ CAST(Number2 AS VARCHAR(20)) +'%' 
                  THEN Number2              
                ELSE OriginalNumber2 END AS Num2
FROM #Testtbl