SQL server : split string function only return part of string

Question

I found this SQL split string function on internet, but when I pass a string contains 700+ items in it, separated by commas, it only generate a table variable with 280 rows, can some help me to identify where's the problem?

The code:

ALTER  FUNCTION [dbo].[fn_Split](@text nvarchar(MAX), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY,
  value nvarchar(MAX)   
)
AS
BEGIN

DECLARE @index int
SET @index = -1 

WHILE (LEN(@text) > 0) 

  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text)  
    IF (@index = 0) AND (LEN(@text) > 0)  
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK  
      END 

    IF (@index > 1)  
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @index))  
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END
  RETURN

END

This is the code I used to test it:

 SELECT * FROM fn_Split(@string,',');

@String is nvarchar(MAX) contains 700+ items, but it only returns a table with 280 rows.

The code you use to test refers a function called LRMWEB_fn_Split, yet the code shown above defines a function called fn_Split. Are you sure you're calling the correct function? Furthermore, how long is your @string-parameter? Note that nvarchar(MAX) cannot hold more than 4000 characters. — Dan
– Dan, Commented Jul 4, 2014 at 18:01
@Dan,Sorry it's just a typo when I write the question, fixed. — SSilicon
– SSilicon, Commented Jul 4, 2014 at 18:02
Ok - what about the length of the content going into the @string-parameter? It will get truncated at 4000 characters, meaning you will only get ~280 items if their average length is 13 characters. — Dan
– Dan, Commented Jul 4, 2014 at 18:04
@Dan, maybe I'm wrong, but I thought nvarchar(MAX) can store up to 2GB of data. — SSilicon
– SSilicon, Commented Jul 4, 2014 at 18:08
It seems you are right, actually. Could you try calling the function with a string consisting of many 1-character items: a,a,a,a,a,a,a ...? Just to see if it still cuts off after 280 items. — Dan
– Dan, Commented Jul 4, 2014 at 18:15

Dave C · Accepted Answer · 2014-07-04 19:33:01Z

1

Here is the XML solution I spoke of...

I'm unclear how you are generating your @string variable, but that method will need to change.

Start your @string with <M> and end it with </M>. Then rather than using a ',' to separate your values, use </M><M> instead.

I tested this in SSMS, by copy/pasting my string value and holding paste until my line number was at well over 20K. The first LEN() actually returned a length of over 19K. I'm showing the datalength() of the xml as well, which is just for demonstration.

I use this method to handle data in a csv-type column in a vendors application db, and turn it into a column of usable data. I have also used the method you show above. This method is much faster than any other method I've tried.

Edit: If this doesn't help -- tell us more. How are you generating this @string, maybe we can propose a better option than stuffing it in a csv string.

Hope this helps, here it is:

DECLARE @string XML
SET @string = '<M>hello</M><M>world</M><M>hello</M><M>world</M><M>hello</M><M>world</M>'

SELECT LEN(CAST(@string AS NVARCHAR(MAX)))
SELECT DATALENGTH(@string)

SELECT Split.a.value('.', 'VARCHAR(MAX)') AS StringVal
FROM  (SELECT @string AS String) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a)
--WHERE LEN(Split.a.value('.', 'VARCHAR(MAX)'))>1

Here is how it could work if your original variable is a VARCHAR in case other people come across this answer in the future.

declare @string2 varchar(max)
set @string2 = 'hello,world,hello,world,hello,world'

SELECT Split.a.value('.', 'VARCHAR(max)') AS String
FROM (SELECT CAST ('<M>' + REPLACE(CAST(@string2 AS VARCHAR(MAX)), ',', '</M><M>') + '</M>' AS XML) AS String) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a)
--WHERE LEN(Split.a.value('.', 'VARCHAR(max)'))>1

If you were holding the csv in a column you'd use something like this:

SELECT DISTINCT A.UserID,  
       Split.a.value('.', 'VARCHAR(max)') AS String
FROM  (SELECT UserID,  
              CAST ('<M>' + REPLACE(CAST(someCSVListColumn AS VARCHAR), ',', '</M><M>') + '</M>' AS XML) AS String  
       FROM #someTable) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a)
--WHERE LEN(Split.a.value('.', 'VARCHAR(max)'))>1

edited Jul 4, 2014 at 19:33

answered Jul 4, 2014 at 19:26

Dave C

7,4821 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SSilicon Over a year ago

sorry, didnt see this at beginning, just tried it in database today, it works really well, thank you so much. One more thing: how to pass this xml parameter in c#? com.Parameters.Add("@projectlist", SqlDbType.NVarChar).Value = test, should I just change nvarchar to xml?

Dave C Over a year ago

I'm not a c# guy, but I believe this might help you aspdotnet-suresh.com/2012/12/…

Venkatesh Yeluri · Accepted Answer · 2014-07-05 06:12:27Z

Try This Function... I don't have time to check your function logic..

Create FUNCTION [dbo].[UDF_Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (ID int Identity(1,1),Value varchar(8000))     
as     
begin     
declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
        set @slice = left(@String,@idx - 1)     
    else     
        set @slice = @String     

    if(len(@slice)>0)
        insert into @temptable(Value) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
end 
return     
end

Collectives™ on Stack Overflow

SQL server : split string function only return part of string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related