select query where condition with in operator and string with %

Spunny 326 Reputation points
2024-02-20T16:53:10.2966667+00:00

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

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
62 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 114.1K Reputation points
    2024-02-20T17:26:17.48+00:00

    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
  2. Sakthivel Ganesan 0 Reputation points
    2024-02-20T17:32:31.8233333+00:00

    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'