Using 'LIKE' with an 'IN' clause full of strings
Solution 1
put the values into a table and use a join
rather than an in
clause:
SELECT * FROM emailaddresses as ea
INNER JOIN addresses as a
ON ea.address like '%' + a.address + '%'
Solution 2
You can use the LOWER function
SELECT * FROM emailaddresses where LOWER(addr) in ('[email protected]', '[email protected]')
Which will convert all addr to lowercase, in which you can then compare the results to what you want.
Solution 3
Note that LIKE
will work either case-sensitively or case-insensitively depending upon which collation is in effect for the expression, but in your case, you have specified no wildcards so there is little point looking to use LIKE
.
The default SQL Server installation is case-insensitive.
If you want a case-insensitive compare because you've got a case-sensitive database, you can cast. I believe this is the appropriate syntax (I've never used it for an IN list on one side of an expression, though).
SELECT *
FROM emailaddresses
WHERE addr COLLATE SQL_Latin1_General_CP1_CI_AS
IN (
'[email protected]' COLLATE SQL_Latin1_General_CP1_CI_AS
,'[email protected]' COLLATE SQL_Latin1_General_CP1_CI_AS
)
A real case for LIKE
would be something for something like addr LIKE '%@google.com"
Solution 4
We can use the 'LIKE-In' approach together in SQL but in somewhat different style, like the one below:
SELECT *
FROM emailaddresses
WHERE addr LIKE '[email protected]' OR addr LIKE '[email protected]'
Related videos on Youtube
John Shedletsky
Tech entrepreneur. Jack of all trades. Stanford class of 2006. Connect on LinkedIn
Updated on July 09, 2022Comments
-
John Shedletsky over 1 year
I want to do a soft string match in a table, like this:
SELECT * FROM emailaddresses where addr in ('[email protected]', '[email protected]')
But if there is an addr value in the table '[email protected]', I want that returned.
Sort of like this:
SELECT * FROM emailaddresses where addr LIKE in ('[email protected]', '[email protected]')
How do I do that?
-
Abe Miessler over 12 years+1, this will work for your needs. If you truly need to use all
like
functionality I would recommend looking at my method. -
Cade Roux over 12 yearsIf his database is actually case-sensitive, LIKE comparisions are still affected by collation (msdn.microsoft.com/en-us/library/ms179859.aspx).
-
djeikyb over 3 yearsthe concat syntax for my db is a double pipe, so the last line for me is
ON ea.address like '%' || a.address || '%'