LIKE matching problem in csv list (RESOLVED)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

LIKE matching problem in csv list (RESOLVED)

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-03-24 : 16:22:20

Hi all,

I want to query a Sent Email Log table to show all the emails sent to a given email address. The EmailLogTable has a ToAddr field that is a comma delimited list of the recipients, so the data looks like this:

EmailLogTable
-------------
abc@me.com
sam@you.com, fred@gmail.com
jane@yahoo.com, al@mail.com, joe@gmail.com

I'm using this query:

SELECT * FROM EmailLogTable WHERE ToAddr LIKE @EmailAddr

The problem is the LIKE is matching too much. If @EmailAddr is 'a' it returns all 3 of the above records.

I want to match only 'whole' email addresses, so I thought of appending a comma (,) to the @EmailAddr, but this does not match when there is only a single recipient (no comma).

I suspect there is a better way.

Any Ideas?
thx

kpg

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 17:28:21

This should work:

SELECT * FROM EmailLogTable WHERE ','+ToAddr+',' LIKE '%,'+@EmailAddr+',%'

But if you have spaces between e-mails, then you may want to remove the spaces as in

SELECT * FROM EmailLogTable WHERE ','+replace(ToAddr,' ','')+',' LIKE '%,'+@EmailAddr+',%'

But then if e-mail addresses themselves have spaces, that would not work. I don't know much about the e-mail address format specifications, but I haven't seen an e-mail address with a space in it, so I think you will be ok.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources