CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Irshad
Irshad

Reputation: 303

Search value in column which is pipe separated in sql

I want to search value which is pipe-separated in column. See below.

Column1 
1
1|2|23
2
6
6|12

I want to search 2 in all rows so it will return below rows

Column1
1|2|23
2

Can anyone please tell me how can we achieve this?

Upvotes: 4

Views: 4146

Answers (3)

Praveen Kumar
Praveen Kumar

Reputation: 1

CREATE FUNCTION [dbo].[udf_Split](@string NVARCHAR(MAX), @delimiter CHAR(1)) 
RETURNS @output TABLE(Id INT IDENTITY(1,1), Value NVARCHAR(MAX)) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (Value)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

SELECT A.* FROM tbl WHERE '2' IN (SELECT value from dbo.udf_Split(col1, '|'))

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166406

How about something like

DECLARE @SearchVal VARCHAR(50) = '2'

SELECT *
FROM YourTable 
WHERE '|' + Column1 + '|' LIKE '%|' + @SearchVal + '|%'

Have a look at the below demo

SQL Fiddle DEMO

Further to this solution, as @marc_s stated above, this is typically the end result of a design gone wrong. I would urge you to step back and rething the solution, as this will only impact you performance/maitinance/sanity wise further down the line.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You can use like:

where '|'+column1+'|' like '%|2|%'

By including the extra delimiters, you avoid having "2" match "23".

Upvotes: 6

Related Questions