0

I want to split column value in table separated by , output each value as separate row

Actual Data User_Prefereces

ID  USERID  Preferences
1   X1234   Football,Tennis,Swimming,Hiking
2   X2345   Cricket
3   X3456   Dancing,Reading
4   X4567   Games,Cricket,Tennis

Expected Output

ID  USERID  Preferences
1   X1234   Football
1   X1234   Tennis
1   X1234   Swimming
1   X1234   Hiking
2   X2345   Cricket
3   X3456   Dancing
3   X3456   Reading
4   X4567   Games
4   X4567   Cricket
4   X4567   Tennis

I use SQL Server 2014 and trying to use below Split function

CREATE FUNCTION [dbo].[FN_SplitString]
( 
    @string NVARCHAR(MAX), 
    @delimiter NCHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT ,@rn INT =1
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter COLLATE SQL_Latin1_General_CP1256_CI_AS, @string COLLATE SQL_Latin1_General_CP1256_CI_AS, @start)
        SET @rn = @rn+1
    END 
    RETURN 
END

Can i get desired output using above function, i tried to use it as but its not working

 SELECT * FN_SplitString(ShowOnPageID,',') AS Col1 FROM Banner

UPDATE *** Possible SOlution***

FUNCTION

CREATE FUNCTION SplitStringsNew
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

SQL To use same

  SELECT b.ID, f.Item FROM User_PreferecesAS b
  CROSS APPLY SplitStringsNew(b.Preferences, ',') as f;

I tried it and its working not sure how good this is in terms or performance

Learning
  • 19,469
  • 39
  • 180
  • 373
  • Honestly SQL Server is not really the right tool for this job. I would probably export the table as CSV, then reformat as you want in Java or maybe even Notepad++. – Tim Biegeleisen Feb 28 '21 at 10:49
  • @TimBiegeleisen, Data keeps change and i want desired out only for frontend feature, I output could be in view or temp table.. Same could be done using C# but then i have call put data in string, split them and then store them in Array or as list.. i was wondering if same could be done using SQL so that frond end logic would be very simple .. – Learning Feb 28 '21 at 10:54
  • Such CSV data should ideally not even be stored in your SQL database. If this requirement is something for production, then don't even think of going this route, even if you get an answer here. – Tim Biegeleisen Feb 28 '21 at 10:58

1 Answers1

0

It's very easy with STRING_SPLIT() and cross apply:

select id,userid, Pref.Value --SplitData
from tabsplit
cross apply STRING_SPLIT (Preferences, ',') Pref

Output:

|id|userid|Value   |
 -- ------ --------
|1 |X1234 |Football|
|1 |X1234 |Tennis  |
|1 |X1234 |Swimming|
|1 |X1234 |Hiking  |
|2 |X2345 |Cricket |
|3 |X3456 |Dancing |
|3 |X3456 |Reading |
|4 |X4567 |Games   |
|4 |X4567 |Cricket |
|4 |X4567 |Tennis  |
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26