Total Page Preview:   000000010950

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-----------
DECLARE @Search VARCHAR(10)
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
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

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 : https://www.linkedin.com



Comments

Counsel
13-Nov-2017
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!

                           
                           

                           

                           

Facebook User: