SQL Server Join with Wildcard and stop on first ma

问题:

    IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
    IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

    CREATE TABLE #TABLE1
    (
        CODE_NAME_T1 NVARCHAR(20)
    )

    CREATE TABLE #TABLE2
    (
        CODE_NAME_T2 NVARCHAR(20)
    )

    INSERT INTO #TABLE1(CODE_NAME_T1)
    VALUES             ('BBX123')
                      ,('BC/230')
                      ,('1AC030')
                      ,('BB01BC')           

    INSERT INTO #TABLE2(CODE_NAME_T2)
    VALUES             ('BB')
                      ,('BC')

    SELECT T1.CODE_NAME_T1, T2.CODE_NAME_T2
    FROM #TABLE1 T1
    LEFT OUTER JOIN #TABLE2 T2
    ON T1.CODE_NAME_T1 LIKE '%' + T2.CODE_NAME_T2 + '%'

    IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
    IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

RESULT

CODE_NAME_T1   |    CODE_NAME_T2
---------------|-----------------
BBX123         |     BB
BC/230         |     BC
1AC030         |     NULL
BB01BC         |     BB
BB01BC         |     BC

Hi, in above code I am using wildcard in join. The problem I am facing that in result "BB01BC" row is appearing twice as it contains both "BB" and "BC" characters. Is there way that it only appears once. So if "BB" is matched in "BB01BC" then it should not look for "BC" in it? Basically only doing one match/lookup and not doing more match/lookup?

回答1:

Here is one method using OUTER APPLY:

SELECT T1.CODE_NAME_T1, T2.CODE_NAME_T2
FROM #TABLE1 T1 OUTER APPLY
     (SELECT TOP 1 t2.*
      FROM #TABLE2 T2
      WHERE T1.CODE_NAME_T1 LIKE '%' + T2.CODE_NAME_T2 + '%'
     ) T2;

Note: You almost always want an ORDER BY when using TOP. You don't seem particularly interested in which row from T2 matches, you just want one of them. If you have a particular priority, then add ORDER BY for prioritization.

回答2:

This seems to work for your situation: SELECT T1.CODE_NAME_T1, (SELECT TOP 1 T2.CODE_NAME_T2 from #TABLE2 T2 where T1.CODE_NAME_T1 LIKE '%' || T2.CODE_NAME_T2 || '%') FROM #TABLE1 T1