Search code examples
sqlsql-serverintersect

Find rows where column1 matches column2 (possibly in an another row)

Say I have some code below from a table called Table1. 

Column1   | Column2
41391175  | 41000
41523664  | 41523
110505116 | 110509
110453629 | 110505
41000     | 351592

Column1 and Column2 are NVARCHAR(10). What I want to return is all entries in Column 1 where Column2 is IN Column1, so I want a result to look like this-

Column1
41523664
110505116
41000

Right now, this is what my code would look like-

SELECT Column1
FROM Table1
INTERSECT  
SELECT Column2
FROM Table1 ;

But as of right now, that only finds the EXACT same number as in Column2, rather than one that Column1 contains inside it, so I would just get this result-

Column1

41000

Is there a way to work around this, or to get an IN clause within INTERSECT? I haven't been able to find something that does that after some research.

Solution

  • I suppose you can match them using LIKE operator:

    SELECT Column1
FROM Table1 AS t
WHERE EXISTS (
    SELECT 1
    FROM Table1 AS x
    WHERE t.Column1 LIKE x.Column2 + '%'
)

    Demo on DB Fiddle