8

I want to do a soft string match in a table, like this:

SELECT * FROM emailaddresses where addr in ('john@google.com', 'jim@google.com')

But if there is an addr value in the table 'JOHN@google.com', I want that returned.

Sort of like this:

SELECT * FROM emailaddresses where addr LIKE in ('john@google.com', 'jim@google.com')

How do I do that?

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
John Shedletsky
  • 7,110
  • 12
  • 38
  • 63
  • Duplicate? http://stackoverflow.com/questions/4335359/is-it-possible-to-use-like-and-in-for-a-where-statment – Stefan Steinegger Apr 05 '11 at 18:01
  • All the addresses are formatted in a non-standard way - so just tolower-ing won't necessarily catch them all – John Shedletsky Apr 05 '11 at 18:05
  • For me this is a duplicate of this (http://stackoverflow.com/questions/3732246/mysql-in-with-like) but I'm sure *someone* would protest about it not being an exact duplicate because it namechecks MySQL. – onedaywhen Apr 11 '11 at 13:45

6 Answers6

12

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 + '%'
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • If his database is actually case-sensitive, LIKE comparisions are still affected by collation (http://msdn.microsoft.com/en-us/library/ms179859.aspx). – Cade Roux Apr 05 '11 at 18:16
  • the concat syntax for my db is a double pipe, so the last line for me is `ON ea.address like '%' || a.address || '%'` – djeikyb Jul 13 '20 at 22:40
2

You can use the LOWER function

SELECT * FROM emailaddresses where LOWER(addr) in ('john@google.com', 'jim@google.com')

Which will convert all addr to lowercase, in which you can then compare the results to what you want.

Mike Lewis
  • 63,433
  • 20
  • 141
  • 111
  • 1
    +1, this will work for your needs. If you truly need to use all `like` functionality I would recommend looking at my method. – Abe Miessler Apr 05 '11 at 18:07
1

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 'john@google.com' OR addr LIKE 'jim@google.com'
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Deepak Tekchandani
  • 488
  • 2
  • 5
  • 15
1

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 (
    'john@google.com' COLLATE SQL_Latin1_General_CP1_CI_AS
    ,'jim@google.com' COLLATE SQL_Latin1_General_CP1_CI_AS
)

A real case for LIKE would be something for something like addr LIKE '%@google.com"

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
0

Try this using a cross join to a table containing a list of email's you want to search for:

declare @email table(
email_check nvarchar(500) not null)

insert into @email(email_check)
values('jack@google.com')
insert into @email(email_check)
values('john.@google.com')



select hit, ID, EMAIL_ADDRESS from (
    select CHARINDEX(email_check, lower(EMAIL_ADDRESS)) hit, ID, EMAIL_ADDRESS
    from Table_With_Email_Addresses
    ,@email
) t
where hit > 0

No need for a "like" since it will parse a string to find a match. Cheers!

artofsql
  • 613
  • 4
  • 11
0
select * from HotelAmenities_ 
where Pamenities in (
            select distinct(pamenities) 
            from HotelAmenities_ 
            where pamenities like '%Swimming%'
        )
Pedro
  • 11,514
  • 5
  • 27
  • 40
Prakash Singh
  • 1