Parsing A Sql Field In A Query

I've inherited a database of user profile information which has a column for personal interests. Multiple interests are separated by a pipe (|). In a SQL query, how can I split a

Solution 1:

The exact syntax depends on which dbms you are using. Assuming you are using MSSQL this is the general syntax

STRING_SPLIT ( string , separator )

For example

DECLARE@string_to_be_split NVARCHAR(400) ='2|27|33|14|15'SELECTvalueFROM STRING_SPLIT(@string_to_be_split, '|')  
WHERE RTRIM(value) <>'';  

Solution 2:

Edit - Could have sworn that I saw SQL Server

If not 2016, just about any Split/Parse Function will do.

Option 1 - With UDF

Declare@YourTabletable (ID int,Interests varchar(250))
InsertInto@YourTablevalues
(1,'2|27|33|14|15')

Select A.ID
      ,B.*From@YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.Interests,'|') B

Option 2 - Without a UDF

Select A.ID
      ,B.*From@YourTable A
 Cross Apply (
                Select RetSeq =Row_Number() over (OrderBy (Selectnull))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x =Cast('<x>'+ replace((Select replace(A.Interests,'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>'as xml).query('.')) as X
                Cross Apply x.nodes('x') AS B(i)
             ) B

Both Return

ID  RetSeq  RetVal
1   1       2
1   2       27
1   3       33
1   4       14
1   5       15

The UDF if Interested

CREATEFUNCTION[dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
ReturnsTableAsReturn (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
    Cross Apply x.nodes('x') AS B(i)
);
--ThanksShnugoformakingthisXMLsafe--Select * from[dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from[dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from[dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')