T-SQL: find match of columns in row (LIKE but different)
Server : SQL Server 2008 R2
I apologize in advance as I am not sure what is the best way to verbalize the question. I am getting a string of email addresses and I need to find out if any of the addresses exist within that string as a user. A query that obviously doesn't work is shown below, but hopefully it helps 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" statement that would check for matches "within a string", but my Google capabilities must be weak today.
Any help is appreciated. If there just isn't a way, I'll have to go in and do some work in code to strip each element in the string and search on each one.
Thanks in advance, Beems
source to share
Split the input line and use IN clause
to split the CSV into lines 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 the 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
source to share
First you need to split the CSV list into a temporary table and then use it INNER JOIN
with your existing table as this 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 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;
Note that the link to "dbo.Splitter" is a placeholder for any line separator you already own or can receive. Don't use a splitter that uses a loop WHILE
. The best options are SQLCLR- or XML-based. XML-based ones are generally fast, but have some encoding issues if the string to be split has XML special characters such as &
, <
or "
. If you want a fast and simple SQLCLR based splitter, you can download the free version of the SQL # library (which I am the creator, but this feature is in the free version) that contains String_Split
and String_Split4k
(if the input is always <= 4000 characters).
source to share
SQL has functions CONTAINS
and IN
. You can use any of them to accomplish your task. Click for any additional information via the MSDN website! Hope this helps.
CONTAINS
will see if any values in your data will contain the entire specified string. View similar in presentationsLIKE '%myValue%';
SELECT f_emailaddress
FROM tb_users
WHERE CONTAINS (f_emailaddress, 'user1@domain.com');
IN
will return matches for any values in the specified comma delimited list. However, they must be exact matches. You cannot provide partial conditions.
SELECT f_emailaddress
FROM tb_users
WHERE f_emailaddress IN ('user1@domain.com','user2@domain.com')
Regarding splitting each of the values into separate lines, have a look at the found StackOverflow question HERE . This may indicate the right direction.
source to share
--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 + '%'
source to share