STRING_SPLIT - How to compare values between 2 tables-CodePudding

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

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,','........?

CodePudding user response：

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