Using "LIKE" with a "IN" clause full of lines

Question

I want to do soft string matching in a table, for example:

SELECT * FROM emailaddresses where addr in ('[e-mail]', '[e-mail]')

But if the table " [e-mail] " has the value addr, I want this to be returned.

Example:

SELECT * FROM emailaddresses where addr LIKE in ('[e-mail]', '[e-mail]')

How to do it?

Answer 1

put the values ​​in a table and use a clause join, not in:

SELECT * FROM emailaddresses as ea
INNER JOIN addresses as a
ON ea.address like '%' + a.address + '%'

Answer 2

You can use the LOWER function

SELECT * FROM emailaddresses where LOWER(addr) in ('[e-mail]', '[e-mail]')

Which converts all addr to lowercase, in which you can compare the results with what you want.

Answer 3

, LIKE , , , , , LIKE.

SQL Server .

, , . , ( IN ).

SELECT *
FROM emailaddresses
WHERE addr COLLATE SQL_Latin1_General_CP1_CI_AS 
IN (
    '[e-mail]' COLLATE SQL_Latin1_General_CP1_CI_AS
    ,'[e-mail]' COLLATE SQL_Latin1_General_CP1_CI_AS
)

LIKE - addr LIKE '[e-mail]"

Answer 4

Try using a cross join in a table containing a list of email addresses you want to find:

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

insert into @email(email_check)
values('[e-mail]')
insert into @email(email_check)
values('[e-mail]')



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 to “seem like”, because it will parse the string to find a match. Hooray!

Answer 5

select * from HotelAmenities_ 
where Pamenities in (
            select distinct(pamenities) 
            from HotelAmenities_ 
            where pamenities like '%Swimming%'
        )

Answer 6

We can use the "LIKE-In" approach together in SQL, but in a slightly different style, for example, below:

SELECT * 
FROM emailaddresses 
WHERE addr LIKE '[e-mail]' OR addr LIKE '[e-mail]'