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

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.

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
– Stu, Commented Jul 14, 2021 at 9:02
Does this answer your question? Split function equivalent in T-SQL? — Joe
– Joe, Commented Jul 14, 2021 at 9:18

Josue Barrios · Accepted Answer · 2021-07-14 13:46:10Z

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

Gordon Linoff · Accepted Answer · 2021-07-15 12:36:27Z

0

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.

answered Jul 15, 2021 at 12:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related