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 '%user1@domain.com,user2@domain.com%'

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

String split is a very common function. If you need help then I suggest you post a question for the code behind environment you use. — paparazzo, Dec 16 '14 at 15:53
Read the following articles to understand what is going on in the answer - [Split strings the right way – or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings : A Follow-Up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up), [Splitting Strings : Now with less T-SQL](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) - But take note, that what you wanted to avoid (doing it in code behind) is actually the best solution. — GarethD, Dec 16 '14 at 16:25

Solomon Rutzky · Answer 1 · 2014-12-16T16:02:59.030

You first need to split the CSV list into a temp table and then use that to INNER JOIN with your existing table, as that will act as a filter.

You cannot use CONTAINS unless you have created a Full Text index on that table and column, which I doubt is the case here.

For example:

CREATE TABLE #EmailAddresses (Email NVARCHAR(500) NOT NULL);

INSERT INTO #EmailAddress (Email)
   SELECT split.Val
   FROM   dbo.Splitter(@IncomingListOfEmailAddresses);

SELECT usr.f_emailaddress
FROM tb_users usr
INNER JOIN #EmailAddresses tmp
        ON tmp.Email = usr.f_emailaddress;

Please note that the reference to "dbo.Splitter" is a placeholder for whatever string splitter you already have or might get. Please do not use any splitter that makes use of a WHILE loop. The best options are either the SQLCLR- or XML- based ones. The XML-based ones are generally fast but do have some issues with encoding if the string to be split has special XML characters such as &, <, or ". If you want a quick and easy SQLCLR-based splitter, you can download the Free version of the SQL# library (which I am the creator of, but this feature is in the free version) which contains String_Split and String_Split4k (for when the input is always <= 4000 characters).

score 1 · Accepted Answer · answered Dec 16 '14 at 16:00

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('user1@domain.com,user2@domain.com', ',', '</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('user1@domain.com,user2@domain.com', ',', '</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('user1@domain.com,user2@domain.com', ',', '</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

Well, I'm not yet sure "how" this works, but it does, so thank you. Sincerely, thank you. I'll peel this apart and do some reading so I can understand it. — Beems, Dec 16 '14 at 16:22
@Beems This link may help you http://oops-solution.blogspot.in/2011/10/sql-serverhow-to-split-comma-delimited.html — Pரதீப், Dec 16 '14 at 16:24

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

SQL has a CONTAINS and an IN function. You can use either of those to accomplish your task. Click on either for more information via MSDNs website! Hope this helps.

CONTAINS

CONTAINS will look to see if any values in your data contain the entire string you provided. Kind of similar in presentations to LIKE '%myValue%';

SELECT f_emailaddress
FROM tb_users
WHERE CONTAINS (f_emailaddress, 'user1@domain.com');

IN

IN will return matches for any values in the provided comma delimited list. They need to be exact matches however. You can't provide partial terms.

SELECT f_emailaddress
FROM tb_users
WHERE f_emailaddress IN ('user1@domain.com','user2@domain.com')

As far as splitting each of the values out into separate strings, have a look at the StackOverflow question found HERE. This might point you in the proper direction.

edited May 23 '17 at 11:50

Community

1
1

answered Dec 16 '14 at 15:40

Volearix

1,413
3
20
46

Thanks for the reply. It looks like both of those are going to force me to split the string from the format in which it is received. Unfortunately, I receive it like the following: "user1@domain.com,user2@domain.com" To do either CONTAINS or IN, it looks like I will need to split it. I can do it, I was just hoping for an easier solution. No big deal I guess, I'll adapt. – Beems Dec 16 '14 at 15:44
@Beems Okay, have a look at this article http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x – Volearix Dec 16 '14 at 15:45
So, for example, the CONTAINS query gives me an error: SELECT f_emailaddress FROM tb_users WHERE CONTAINS (f_emailaddress, 'user1@domain.com,user2@domain.com'); ERROR: "Syntax error near ',' in the full-text search condition" – Beems Dec 16 '14 at 15:50
@Beems and Volearix: `CONTAINS` only works on full-text indexed columns. – Solomon Rutzky Dec 16 '14 at 15:59

HaveNoDisplayName · Answer 4 · 2014-12-16T16:17:14.097

0

You can try like this(not tested).

Before using this, make sure that you have created a Full Text index on that table and column.

Replace your comma with AND then

SELECT id,email
FROM t
where CONTAINS(email, 'user1@domain.com and user2@domain.com');

edited Dec 16 '14 at 16:17

answered Dec 16 '14 at 15:50

HaveNoDisplayName

7,711
106
32
44

@srutzky Thanks for telling me. I know that. – HaveNoDisplayName Dec 16 '14 at 16:00
1

@Piyush If you know this, and it is relevant to the answer it is probably a good idea to include it in your answer. As you are giving this as the solution to a problem it is very likely the person that has asked the question does not know this, people very rarely criticise an answer for containing too much detail! – GarethD Dec 16 '14 at 16:08
@srutzky and GarethD:- added in answer – HaveNoDisplayName Dec 16 '14 at 16:18

score 0 · Answer 5 · answered Dec 16 '14 at 21:55

--prepare temp table for testing
DECLARE @tb_users AS TABLE
    (f_emailaddress VARCHAR(100))
INSERT  INTO @tb_users
        ( f_emailaddress)
VALUES  ( 'user1@domain.com' ),
        ( 'user2@domain.com' ),
        ( 'user3@domain.com' ),
        ( 'user4@domain.com' )
--Your query
SELECT  f_emailaddress
FROM    @tb_users
WHERE   'user1@domain.com,user2@domain.com' LIKE '%' + f_emailaddress + '%'

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

5 Answers5