Total Page Preview:   000000017316

SQL Server - Like search record any value from column to stored in comma separate value(CSV).

In this blog I am going to explain SQL Server - Like search record any value from column to stored in comma separate value(CSV).
Example:  I have stored data to comma separated value into the column : '1,25,11,21,41,61,91,22'. So I want to search only 1 stored all column not apart from 1 like 11,21 etc.

There is one tricky scenario. If I am looking for '1' in the list '1,25,11,21,41,61,91,22' then it would find ",1" and return that incorrect entry. This takes care of all solutions:

--------Search record to any value in categoryId which is store in CSV-----------
SET @Search = '1'

SELECT [Id] ,[Name] ,[CategoryId] FROM [tblStudents] 

WHERE ','+ [CategoryId] + ',' like '%,' + @Search + ',%'


Like Search value in CSV column
Get Table and Data Insert SQL Scripts:
---------Create student Table-------

CREATE TABLE [dbo].[tblStudents](
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[CategoryId] [varchar](50) NOT NULL


---------Insert record into student table-------------
INSERT [dbo].[tblStudents] ([Id], [Name], [CategoryId]) VALUES (1,N'Brijesh Kumar',  N'1,25,11,21,41,61,91,22')
INSERT [dbo].[tblStudents] ([Id], [Name], [CategoryId]) VALUES (2,N'Dev Karan',  N'22,21,11,51,35,42,61')
INSERT [dbo].[tblStudents] ([Id], [Name], [CategoryId]) VALUES (3,N'Ajay Kumar',  N'2,44,11,52,31,51,421,71')
INSERT [dbo].[tblStudents] ([Id], [Name], [CategoryId]) VALUES (4,N'Sanny Kumar', N'21,22,24,36,51,1')
INSERT [dbo].[tblStudents] ([Id], [Name], [CategoryId]) VALUES (5,N'Suresh',  N'21,25,1,25,42,81')
INSERT [dbo].[tblStudents] ([Id], [Name], [CategoryId]) VALUES (6,N'Rohit', N'21,2,52,15,65')


Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :


That is a really good tip particularly to those new to the blogosphere. Brief but very accurate info… Many thanks for sharing this one. A must read post!
Hello! I juѕt would like to give you a big thumbs up for the excellent info you have g᧐t here on this post. I'll be coming ƅacк to your blog for more soon.
I aam reցular reader, hoow are ʏou eѵeryƄody? This piece of writing posted at this web page is truly pleasant.
Ɍeally nice layout and good content, practicaⅼly nothing else we need :D.




Facebook User: