STRING_SPLIT – How to compare values between 2 tables

I have 2 tables. One table has an Invoice field with values like this – one invoice/value for each row.

 Invoice
    1234
    6666
    8867
    6754
    8909

I have second table with an ‘Invoices’ field with values delimited – like this

 Invoices
    1234,6666,9999
    8595,0904,8090
    4321

How do I select – match the rows – invoice records in table 1 to the invoices in table 2.

Use Split_string ?? Something like this??

SELECT *
FROM TABLE1
WHERE INVOICE IN  (SELECT SPLIT_STRING(INVOICES,','........?

>Solution :

You would have to normalize your delimited string via a CROSS APPLY

Select  *
 From  Table1
 Where Invoice in (
                    Select B.Value 
                     From  Invoices A
                     Cross Apply string_split([Invoices],',') B
                  ) 

Note this could be a JOIN as well