select query where condition with in operator and string with %

Question

Hi, Below query is not working with in operator and string having % sign.

DECLARE @types Table (
       ID int,
       type varchar(50)
       mailtype varchar(50)
)
INSERT INTO @types (ID, type, mailtype)
SELECT 1, 'Redem', 'ABS_HOME'
UNION
SELECT 2, 'Redem2', 'ABS_AUTO'
UNION
SELECT 3, 'Redem3', 'AYY_CMO'
union
select 4, 'Redem4', 'CORP'
UNION
select 5, 'Redem5', 'CMBS'
union
select 6, 'redem6', 'MBS_COOL'
UNION
SELECT 7, 'redem7', 'SBP'
 
 
-- Need all to be returned 1, 2, 3, 5, 6, and 7
select * from @types where mailtype IN ('ABS%', '%MBS%', '%CMO', 'SBP')
 
--below query should return 4
select * from @types where mailtype NOT IN ('ABS%', '%MBS%', '%CMO', 'SBP')
 
 But 1st query is returning only SBP row
and second query returning all rows except 'SBP' row  

please suggest how to do in with percent strings

Thank You

Answer 1

For example:

select ID, type, mailtype 
from @types, (values ('ABS%'), ('%MBS%'), ('%CMO'), ('SBP')) t(p)   
where mailtype like p

select ID, type, mailtype
from @types
except
select ID, type, mailtype
from @types, (values ('ABS%'), ('%MBS%'), ('%CMO'), ('SBP')) t(p)
where mailtype like p

Answer 2

Hi, Wildcard character filter would work only with LIKE operator not with any other operators. If you use it with any other operators, it will be considered as a percentage character instead of wild card. So, it can be replaced as

select * from @types 
where mailtype like 'ABS%' or mailtype like '%MBS%' or mailtype like '%CMO' or mailtype like 'SBP'