Total Page Preview:   000000011418

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!

                           
                           

                           

                           

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 all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
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
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-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 are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: