Searh Comma Separated String (single character) without SPLIT the Parameter


We can take advantage of the the wildcard match [] with LIKE in TSQL for single letter search.
Here is a code snippet:


CREATE TABLE Testa ( id INT, col1 VARCHAR(100))
INSERT INTO Testa VALUES (1,'a,b,c,d')
,(2,'d,e,f,g')
, (3,'a,s,k')
, (4,'t')


declare @s varchar (20)='a,z,t'
select *
from testa
where ','+col1+',' like '%,'+quotename(@s)+',%'

--drop  table testa

 

http://msdn.microsoft.com/en-us/library/ms179884.aspx
http://msdn.microsoft.com/en-us/library/ms179859.aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s