T-SQL: Find column match within a string (LIKE but different)

Question

Server: SQL Server 2008 R2

I apologize in advance, as I'm not sure of the best way to verbalize the question. I'm receiving a string of email addresses and I need to see if, within that string, any of the addresses exist as a user already. The query that obviously doesn't work is shown below, but hopefully it helps to clarify what I'm looking for:

SELECT f_emailaddress
FROM tb_users
WHERE f_emailaddress LIKE '%[email protected],[email protected]%'

I was hoping SQL had an "InString" operator, that would check for matches "within the string", but I my Google abilities must be weak today.

Any assistance is greatly appreciated. If there simply isn't a way, I'll have to dig in and do some work in the codebehind to split each item in the string and search on each one.

Thanks in advance, Beems

Answer 1

Split the input string and use IN clause

to split the CSV to rows use this.

SELECT Ltrim(Rtrim(( Split.a.value('.', 'VARCHAR(100)') )))
FROM   (SELECT Cast ('<M>'
                     + Replace('[email protected],[email protected]', ',', '</M><M>')
                     + '</M>' AS XML) AS Data) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 

Now use the above query in where clause.

SELECT f_emailaddress
FROM   tb_users
WHERE  f_emailaddress IN(SELECT Ltrim(Rtrim(( Split.a.value('.', 'VARCHAR(100)') )))
                         FROM   (SELECT Cast ('<M>'
                                              + Replace('[email protected],[email protected]', ',', '</M><M>')
                                              + '</M>' AS XML) AS Data) AS A
                                CROSS APPLY Data.nodes ('/M') AS Split(a)) 

Or use can use Inner Join

SELECT f_emailaddress
FROM   tb_users A
       JOIN (SELECT Ltrim(Rtrim(( Split.a.value('.', 'VARCHAR(100)') )))
             FROM   (SELECT Cast ('<M>'
                                  + Replace('[email protected],[email protected]', ',', '</M><M>')
                                  + '</M>' AS XML) AS Data) AS A
                    CROSS APPLY Data.nodes ('/M') AS Split(a)) B
         ON a.f_emailaddress = b.f_emailaddress