Total Page Preview:   000000011956

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!
Rhem
16-Oct-2021
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.
Mulga
03-Nov-2021
Ɍeally nice layout and good content, practicaⅼly nothing else we need :D.
Gehlert
04-Nov-2021
I aam reցular reader, hoow are ʏou eѵeryƄody? This piece of writing posted at this web page is truly pleasant.

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: