Splitting a string of unlimited length

re: Splitting a string of unlimited length

Tiraj — Wed, 14 Mar 2007 06:15:54 GMT

Well it says "Using the function is quite simple"

But im having problems (errors) even while tring to create the function...

re: Splitting a string of unlimited length

Adam Machanic — Sun, 18 Mar 2007 16:13:32 GMT

What error are you seeing? It works fine on this end...

re: Splitting a string of unlimited length

Ali — Thu, 19 Apr 2007 06:22:56 GMT

It works fine. but when I use it in a Stored Procedure it gives error:

Incorrect syntax near 'SplitString'.

re: Splitting a string of unlimited length

Asghar — Thu, 29 Nov 2007 04:55:20 GMT

Hi Adam this is a great function but I have got string with such as ("hello","sdfsfs",....)

My problem is how can I pass two parremeters such as , and "".

and how can I insert the values into a table

Thanks

re: Splitting a string of unlimited length

David — Tue, 05 Feb 2008 21:07:30 GMT

Had to change the last section of the code to get it to work beyond 8,000 characters. Great approach though.

_________________________________

--Start next chunk

SET @SplitStart = @SplitEnd + 1

SET @SplitEnd = @SplitEnd + 7998

IF ( @SplitEnd >= DATALENGTH(@List) )

SET @SplitEnd = DATALENGTH(@List) +1

ELSE

BEGIN

SELECT @SplitEnd = @SplitStart + MAX(Number)

FROM dbo.Numbers

WHERE (SUBSTRING(@List, Number, 1) = @Delimiter

OR Number = DATALENGTH(@List) + 1

)

AND Number BETWEEN @SplitStart AND @SplitEnd

END

re: Splitting a string of unlimited length

David — Tue, 05 Feb 2008 21:36:04 GMT

Please ignore the previous post - that didn't fix it. The issue seemed to be with parsing the last number in the last chunk. I added

AND CHARINDEX(',', @LeftSplit, Number + 1) > 0

to the key SELECT statement.

Pattern-based split string

Adam Machanic — Sun, 25 Jan 2009 19:52:17 GMT

"hickymanz" asked in the SQL Server Central forums for a method of counting unique words in a text column.

Tokenize UDF

Adam Machanic — Sun, 25 Jan 2009 19:54:25 GMT

Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting.

re: Splitting a string of unlimited length

Craig Hathaway — Mon, 23 Feb 2009 05:15:34 GMT

Hi, this function times out when the delimiter is the <space> character - for splitting sentences into words (etc). I suspect thsi has to do with trimming...

E.g.

SELECT *

FROM dbo.[SplitString]( 'test one, two, three',' ')

re: Splitting a string of unlimited length

Adam Machanic — Mon, 23 Feb 2009 16:55:23 GMT

Hi Craig,

Busy day at the office? Nothing better to do than rip apart my old blog posts? <g>

The function has been fixed... Thanks for reporting this!

re: Splitting a string of unlimited length

techvslife — Sun, 19 Jul 2009 06:04:26 GMT

Thanks. I notice Erland has a new version, & it will split nvarchar(max), i.e. more than 8000 chars: (long article but search for "Chunking Multi-Statement Function")

http://www.sommarskog.se/arrays-in-sql-2005.html

(& btw, isn't text type deprecated now.)

(haven't tested both yet.)

re: Splitting a string of unlimited length

TechVsLife — Sun, 19 Jul 2009 16:22:38 GMT

I altered Erland's function to include wordposition and separator position (maybe in a highly inefficient way, but it seems to work correctly).

To test it on large strings, you can run something like:

SELECT * FROM dbo.tfnSplitString(REPLICATE(CAST(',AB,CD,EFG,HI,MNZ,QR,UVW,YZ1' AS nvarchar(max)), 6000), DEFAULT) ORDER BY WordPos;

(Note that my numbers table is called "Number" and the number column in it is "Num.")

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[tfnSplitString] (

@StringToSplit NVARCHAR(MAX),

@Separator NCHAR(1) = N','

)

RETURNS @t TABLE (Word nvarchar(4000) NOT NULL, WordPos int not null, SepPos int not null) AS

BEGIN

DECLARE @slices TABLE (

slice nvarchar(4000) NOT NULL,

slicenum int NOT NULL)

DECLARE @slice nvarchar(4000),

@textpos int = 1,

@maxlen int = 4000-2,

@stoppos int,

@slicenum int = 0

WHILE datalength(@StringToSplit) / 2 - (@textpos - 1) >= @maxlen

BEGIN;

SET @slice = substring(@StringToSplit, @textpos, @maxlen);

SET @stoppos = @maxlen -

charindex(@Separator COLLATE Latin1_General_100_BIN2,

reverse(@slice));

INSERT @slices (slice, slicenum)

VALUES (@Separator + left(@slice, @stoppos) + @Separator, @slicenum);

SET @textpos = @textpos - 1 + @stoppos + 2;

SET @slicenum +=1;

-- On the other side of the comma.

END;

INSERT @slices (slice, slicenum)

VALUES (@Separator + substring(@StringToSplit, @textpos, @maxlen) + @Separator, @slicenum);

WITH wordget (Word, SepPos) AS (

SELECT ltrim(rtrim(substring(s.slice, N.Num + 1,

charindex(@Separator COLLATE Latin1_General_100_BIN2,

s.slice, N.Num + 1) -

N.Num - 1))),

charindex(@Separator COLLATE Latin1_General_100_BIN2,

s.slice, N.Num + 1)+(slicenum*@maxlen)-1

FROM Number N

JOIN @slices s

ON N.Num <= len(s.slice) - 1

AND substring(s.slice, N.Num, 1) = @Separator COLLATE Latin1_General_100_BIN2

)

INSERT @t (Word, WordPos, SepPos)

SELECT Word, ROW_NUMBER() OVER (ORDER BY SepPos) AS WordPos, SepPos FROM wordget;

RETURN;

END

re: Splitting a string of unlimited length

TechVsLife — Sun, 19 Jul 2009 20:12:56 GMT

I made a one-off error (--not the first time):

replace "(slicenum*@maxlen)-1"

with

"(slicenum*(@maxlen-1))-1"

(the mistake only occurs once, or only the 'zeroth' time, depending on how you count.)

re: Splitting a string of unlimited length

TechVsLife — Sun, 19 Jul 2009 23:18:44 GMT

Sorry, recorrected (one hopes for the last time)--it wasn't a one-off error, though it looked like it. (--also, there may be a more efficient approach to adding separator position.)

NOTE: assumes the existence of a numbers table called 'Number' with an integer primary key column 'Num' that has consecutive integers (requires 4,000).

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[tfnSplitString] (

@StringToSplit NVARCHAR(MAX),

@Separator NCHAR(1) = N','

)

RETURNS @t TABLE (Word nvarchar(4000) NOT NULL, WordPos int not null, SepPos int not null) AS

BEGIN

assumes the existence of a numbers table called 'Number' with an integer primary key column 'Num'.

DECLARE @slices TABLE (

slice nvarchar(4000) NOT NULL,

RunningSliceLen int NOT NULL)

DECLARE @slice nvarchar(4000),

@textpos int = 1,

@maxlen int = 4000-2,

@stoppos int,

@RunningSliceLen int = -1

WHILE datalength(@StringToSplit) / 2 - (@textpos - 1) >= @maxlen

BEGIN;

SET @slice = substring(@StringToSplit, @textpos, @maxlen);

SET @stoppos = @maxlen -

charindex(@Separator COLLATE Latin1_General_100_BIN2,

reverse(@slice));

INSERT @slices (slice, RunningSliceLen)

VALUES (@Separator + left(@slice, @stoppos) + @Separator, @RunningSliceLen);

SET @textpos = @textpos - 1 + @stoppos + 2;

SET @RunningSliceLen = @RunningSliceLen + DATALENGTH(@Separator + left(@slice, @stoppos))/2;

-- On the other side of the comma.

END;

INSERT @slices (slice, RunningSliceLen)

VALUES (@Separator + substring(@StringToSplit, @textpos, @maxlen) + @Separator, @RunningSliceLen);

WITH wordget (Word, SepPos) AS (

SELECT ltrim(rtrim(substring(s.slice, N.Num + 1,

charindex(@Separator COLLATE Latin1_General_100_BIN2,

s.slice, N.Num + 1) -

N.Num - 1))),

charindex(@Separator COLLATE Latin1_General_100_BIN2,

s.slice, N.Num+1)+(s.RunningSliceLen)

FROM Number N

JOIN @slices s

ON N.Num <= len(s.slice) - 1

AND substring(s.slice, N.Num, 1) = @Separator COLLATE Latin1_General_100_BIN2

)

INSERT @t (Word, WordPos, SepPos)

SELECT Word, ROW_NUMBER() OVER (ORDER BY SepPos) AS WordPos, SepPos FROM wordget;

RETURN;

END