CODEWITHSUNDEEP
sqlsql-server
user1619480
user1619480

Reputation: 543

SQL Server temporary table split string

I have a requirement to read a file into memory and pass the string into a stored procedure.

I am required to split each new line of that string into a new row in a temporary database for processing.

Does anyone have a function that will convert a big string (1000 line) each line with around 500 characters into a temporary database.

Thanks

Upvotes: 0

Views: 804

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

Create a split function (this one will handle strings up to about 4.5 million characters, depending on the version of SQL Server):

CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number), Item 
    FROM (SELECT Number, Item = 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 JOIN sys.all_objects AS s2) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);

So then you pass the string in and say:

SELECT LineNumber = [Index], Line = Item
INTO #temp
FROM dbo.SplitStrings_Ordered(@StringParameter, CHAR(13)+CHAR(10)) AS x;

You may have to play with CHAR(10) and CHAR(13) - how CR/LF are stored in the file may vary depending on where they came from.

Upvotes: 3

Related Questions