What is the most efficient way of Splitting the data from sql column and saving it in another table in sql stored procedure

Question

I have a table with the following structure

Id	Data
1	5#SkipData#Data1,Data2,Data3
2	5#SkipData#Data4
3	5#SkipData#Data5,Data6,Data7,Data8,Data9

I want to put the data in another table with the following format:

Id	SeperatedData
1	Data1
1	Data2
1	Data3
2	Data4
3	Data5
3	Data6
3	Data7
3	Data8
3	Data9

The skipdata has length of 5 whereas all data have fixed length 13. The data's are seperated by comma (,) symbol as mentioned in the table. Currently, this is implemented in the stored procedure using cross apply and performance is quite poor.

The following data transformation is done in sql stored procedure.

Have you tried [SPLIT_STRING()](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) ? — Squirrel, Jul 14 '21 at 08:01
That's a shame, `string_split` arrived in SQL 2016, it's orders of magntude faster for your purpose, I would seriously consider upgrading your SQL Server version. — Stu, Jul 14 '21 at 09:02
Does this answer your question? [Split function equivalent in T-SQL?](https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) — Joe, Jul 14 '21 at 09:18
_performance is quite poor_ Because you are storing delimited data in a relational table. Perhaps it would be more efficient to do this ONCE when the rows are inserted or updated and you can materialize the split values into a separate, normalized table where they can be more easily queried. Or maybe the code in your stored procedure (which you did not post) can be improved. — SMor, Jul 14 '21 at 11:07
@SuryanshuSingh . . . Do the data values all have the same length? — Gordon Linoff, Jul 14 '21 at 11:10
@GordonLinoff Yes, all the data values have the same length. — Suryanshu Singh, Jul 15 '21 at 11:50
@SMor - The source data is picked up from a table in sql and the separated data is stored in a temp table where I am performing other operations on it after seperation. In current implementation XML method is used with cross apply to separate the values. Example of current implementation: https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 — Suryanshu Singh, Jul 15 '21 at 11:58

score 1 · Accepted Answer · answered Jul 14 '21 at 13:46

I use this table function to split the data.

CREATE FUNCTION [dbo].[fn_Split_String]
(   
    @Text NVARCHAR(MAX)
    ,@Delimiter CHAR(1)
)
RETURNS @Result TABLE (
    Row INT IDENTITY(1, 1)
    ,String NVARCHAR(MAX)
)
AS
BEGIN

    DECLARE @Lenght INT = LEN(@Text) + 1
            ,@Char CHAR(1) = ''
            ,@Word NVARCHAR(MAX) = ''
            ,@Index INT = 1

    WHILE @Index < @Lenght
        BEGIN
            
            SET @Char = SUBSTRING(@Text, @Index, 1)

            IF (@Char = @Delimiter AND @Word != '')
                BEGIN
                    INSERT INTO @Result VALUES (LTRIM(RTRIM(@Word)))
                    SET @Word = ''
                END
            ELSE
                BEGIN
                    SET @Word += @Char
                END

            SET @Index += 1
        END

    IF (@Word != '')
        BEGIN
            INSERT INTO @Result VALUES (LTRIM(RTRIM(@Word)))
        END

RETURN
END
GO

so, if you use this function your query looks like this

SELECT
    D.Id
    ,S.String
FROM Data D
CROSS APPLY dbo.fn_Split_String(REPLACE(D.Data, '5#SkipData#', ''), ',') S

score 0 · Answer 2 · answered Jul 15 '21 at 12:36

If all the data values have the same length, you can use a brute force approach. You need to generate a list of numbers -- up to the maximum number of data values. Say that is 5:

select d.id, substring(d.data, 12 + n * 6, 5)
from data d cross join
     (values (0), (1), (2), (3), (4)) v(n)
where substring(d.data, 13 + n * 6, 5) <> '';

You can use any method you like to generate the numbers. You might even have a numbers table handy.

Here is a db<>fiddle.

What is the most efficient way of Splitting the data from sql column and saving it in another table in sql stored procedure

2 Answers2