Using 'LIKE' with an 'IN' clause full of strings

27,713

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]'
Share:
27,713

Related videos on Youtube

John Shedletsky
Author by

John Shedletsky

Tech entrepreneur. Jack of all trades. Stanford class of 2006. Connect on LinkedIn

Updated on July 09, 2022

Comments

  • John Shedletsky
    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
    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
    Cade Roux over 12 years
    If his database is actually case-sensitive, LIKE comparisions are still affected by collation (msdn.microsoft.com/en-us/library/ms179859.aspx).
  • djeikyb
    djeikyb over 3 years
    the concat syntax for my db is a double pipe, so the last line for me is ON ea.address like '%' || a.address || '%'

Related