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-----------
DECLARE @Search VARCHAR(10)
SET @Search = '1'
SELECT [Id] ,[Name] ,[CategoryId] FROM [tblStudents]
WHERE ','+ [CategoryId] + ',' like '%,' + @Search + ',%'
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
) ON [PRIMARY]
GO
---------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')
Thanks