CODEWITHSUNDEEP
sql-serversql-server-2008-r2wildcard
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Prepare dynamic WILDCARD

I have the following table Test_01 with two columns namely Cola and Colb.

I have a situation were I need to use LIKE with IN for multiple values.

Table:

SELECT * FROM Test_01;

Cola    Colb 
------------
1       A
11      B
22      C

Example:

DECLARE @a VARCHAR(max) = 'A,B,C'
DECLARE @sql VARCHAR(max) = ''

My bad try: Which does not works

SET @SQL = 'SELECT * from test_01 
        WHERE Colb LIKE IN (''%'+REPLACE(@a,',','%'',''%')+'%'')';
PRINT(@sql);

Finally I came to know that I need to prepare a LIKE with OR condition by referring THIS.

How to prepare a dynamic WILDCARD as shown it below?

SELECT * FROM Test_01
WHERE 
Colb LIKE '%A%' OR 
Colb LIKE '%B%' OR 
Colb LIKE '%C%' ;

Upvotes: 1

Views: 48

Answers (1)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

I'm using a table variable to parse variable @a values and stored them as rows. Check using inner join between main table test_01 and table variable with like to see if any matching records.

DECLARE @a VARCHAR(max) = 'A,B,C';

declare @tablevariable table
(
colb varchar(20)
);

INSERT INTO @tablevariable
SELECT Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT  
         CAST ('<M>' + REPLACE(@a, ',', '</M><M>') + '</M>' AS XML) AS String  
    ) AS A CROSS APPLY String.nodes ('/M') AS Split(a);


select tb1.* from test_01 tb1 inner join @tablevariable tb2
on tb1.colb like '%'+tb2.colb+'%'

Upvotes: 2

Related Questions