Split string and return data in multiple columns

Question

First of all many thanks to the site creator and most importantly helping guru's on this site.

I have the same problem splitting string from a field and displaying it in multiple columns example my table has got three columns dbo.tests

Fname ID wTest                     Loc
ABC    1 "XYZ,PTO,LKMD,HGGFFD,"    R1
BCE    2 "PTO,XYZ,LKMD,,"          R1
LKJ    3 "XYZ"                     R3
JKL    4 "XYZ,PTO,LKMD,HGGFFD,PKL" R2

The output for the select statement should display the data as follows: (Dynamically generate number of columns based on maximum columns required from wTest string and fill the empty columsn with null or some value.

Returns:

Fname ID Loc wTest wTest1 wTest2,wTest3,Wtest4...
ABC    1 R1  XYZ    PTO   LKMD   HGGFFD  Null
BCE    2 R1  PTO    XYZ   LKMD   Null    Null
LKJ    3 R3  XYZ    Null  Null   Null    Null                     
JKL    4 R2  XYZ    PTO   LKMD   HGGFFD  PKL

Two close function I came accross are as follows:

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512)) 
RETURNS table AS RETURN 

(     
WITH Pieces (pn, start, stop) AS 
( 
SELECT 1, 1, CHARINDEX(@sep, @s)       
UNION ALL       
SELECT pn + 1, stop + 1, 
CHARINDEX(@sep, @s, stop + 1)       
FROM Pieces       
WHERE stop > 0     )     

SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s     FROM Pieces   )




with testTable AS 
(  SELECT 1 AS Id, N'how now brown cow' AS txt 
UNION ALL  
SELECT 2, N'she sells sea shells upon the sea shore' UNION ALL  
SELECT 3, N'red lorry yellow lorry' UNION ALL  
SELECT 4, N'the quick brown fox jumped over the lazy dog'  )    

SELECT display_term, COUNT(*) As Cnt   
FROM testTable  CROSS APPLY sys.dm_fts_parser('"' + txt + '"', 1033, 0,0)  
GROUP BY display_term  
HAVING COUNT(*) > 1  ORDER BY Cnt DESC

Any help in this regard is highly appreciated.

Zain... zainali2006@hotmail.co.uk

score 0 · Answer 1 · answered Mar 04 '11 at 10:27

Someone suggested this, but I am having difficulties applying for my purpose....

 Returns @Tbl_IDs 
    Table (Id int identity(1,1),
    Data Varchar(500)) As 
    Begin 
    --Remove the leading delimiter if any
    while (substring(@IDs,1,1) =@Delimiter)
    set @IDs = substring(@IDs, 2,len(@IDs)-1)

    -- Append comma
    --Set @IDs = @IDs + @Delimiter
    set @IDs = REPLACE(RTRIM(LTRIM(REPLACE(@IDs,@Delimiter,' '))),' ',@Delimiter)


    -- Indexes to keep the position of searching
    Declare @Pos1 Int
    Declare @pos2 Int
    Declare @RowNum Int

    -- Start from first character 
    Set @Pos1=1
    Set @Pos2=1
    While @Pos1>0
    Begin 
    Set @Pos1 = CharIndex(@Delimiter,@IDs,@Pos1)
    Insert @Tbl_IDs Values (Substring(@IDs,@Pos2,@Pos1-@Pos2))
    -- Go to next non comma character
    Set @Pos2=@Pos1+1
    -- Search from the next charcater
    Set @Pos1 = @Pos1+1
    End 
    Return
    End

score 0 · Answer 2 · answered Mar 04 '11 at 11:04

Another one I came across quite interesting and simple but not sure how to use in my select statement:

DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS,,TCF'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

Split string and return data in multiple columns

2 Answers2

Linked