SQL SERVER – SEARCH RECORDS WHICH ARE SEPARATED WITH PIPE DELIMITER

Today I got phone call from one of my friend regarding some query to fetch records from one table let's called Person table and column Role  .But Column Role having multiple values separated with comma delimiter . Now he need to find records where input value matches any value in column Role from Person table. Then I have given one stupid answer with long query like by split the record into multiple records based on delimiter and then search record based on input and then club the record. Finally his requirement meets and he is happy but I am very upset with that big query .Then I try to write get same result with some different logic like below.

Table records are like below

We need to find records where input value matches any value in column Role from Person table, in this case our input is ‘6’ and delimiter is comma (,)

DECLARE @INPUT VARCHAR(5)='6'

DECLARE @DELI VARCHAR(5)=','

DECLARE @PERSONROLES TABLE

    (

        IDPERSON INT,

        RAWROLEOPTIONS NVARCHAR(MAX)

    )

INSERT INTO @PERSONROLES VALUES (1, '1,12,3')

INSERT INTO @PERSONROLES VALUES (2, '4,5,6')

INSERT INTO @PERSONROLES VALUES (3, '7')

SELECT * FROM @PERSONROLES WHERE   @DELI+RAWROLEOPTIONS+@DELI  LIKE '%'+@DELI + @INPUT +@DELI+'%'

• email
• facebook
• linkedin
• twitter
• reddit
• google+

Kiran Reddy A

Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL

Get Free Email Updates to your Inbox!

• Rss
• Twitter
• FaceBook
• Google +