Spliting large string in Sql Server

Question

I am using this logic to split the string query

declare @query nvarchar(max)

set @query = '1&2&3&4&5&6&7&8&9&10&11&12&13&14'

SELECT SUBSTRING('&' + @query + '&', Number + 1,     -- is used to split the '@query'   on the basis of '&' sign         
    CHARINDEX('&', '&' + @query + '&', Number + 1) - Number -1)AS VALUE                
    FROM master..spt_values                
    WHERE Type = 'P'                
    AND Number <= LEN('&' + @query + '&') - 1                
    AND SUBSTRING('&' + @query + '&', Number, 1) = '&'

It works fine when query is small, but its giving me less result then actual when the value of @query is very large

For eg.

@query = 'very large string containing 60 & sign '

returns only 10 records

How can I split large string, and what is the reason? Why can SUBSTRING not handle large strings?

Is the string longer than 2048 characters? (`master..spt_values` has 2048 rows where `Type = 'P'`. If your string is longer than 2048 characters, the remainder may be ignored.) — Ed Harper, May 22 '13 at 07:46
@ITBeginner: yes, i want only split by & sign and its very large string — Vijay Singh Rana, May 22 '13 at 07:46
@EdHarper: it may be larger then 2048, then how can i split large string — Vijay Singh Rana, May 22 '13 at 07:47
A typical solution is to `CROSS JOIN` `spt_values` to itself, using a `row_number()` function to generate the sequential values for `Number`. See http://stackoverflow.com/a/1394093/27825. Answers to that question provide several other solutions. — Ed Harper, May 22 '13 at 07:50

score 0 · Answer 1 · answered May 22 '13 at 07:57

You can use this function. It returns a table of splitted values, based on input string and a delimeter.

Usage:

select * 
from dbo.fn_ParseText2Table('1&2&3&4&5&6&7&8&9&10&11&12&13&14','&')

The function has a parameter @p_SourceText which type is varchar(8000), so input string can be up to 8000 characters.

You can change type to varchar(max) if version of your SQL Server allows you to do this.

score 0 · Answer 2 · answered May 22 '13 at 08:17

I used this Function and its working for me Perfectly :)

 CREATE function f_split(@SourceSql nvarchar(Max),@StrSeprate varchar(10))
returns @temp 
table([column] nvarchar(Max))
begin    
 declare @i int    
 set @SourceSql=rtrim(ltrim(@SourceSql)) 
 set @i=charindex(@StrSeprate,@SourceSql)    
 while @i>=1    
 begin        
  insert @temp values(left(@SourceSql,@i-1))        
  set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)        
  set @i=charindex(@StrSeprate,@SourceSql)    
 end    
 if @SourceSql<>'\'       
 insert @temp values(@SourceSql)    
 return 
end
go

    select * from dbo.f_split('1,2,3,4,5',',')
    go

BINU VARGHESE · Answer 3 · 2013-09-19T13:39:37.883

I am not sure why your code is not getting the result. I executed the query with large amount of data. But I got the result. May be your string is very much bigger than I did the testing. I have also the same requirement to split the string. I am using this function to get the solution. You can try this.

CREATE FUNCTION [dbo].[fnSplitString] ( @MyArray VARCHAR(8000), @separator CHAR(1) ) 
    RETURNS @RetTable TABLE 
    (StrValue VARCHAR(256))
AS

BEGIN
DECLARE @SeperatorString VARCHAR(10);
SET @SeperatorString = '%' + @separator + '%'
DECLARE @separator_position INT 
DECLARE @array_value VARCHAR(1000) 
SET @MyArray = @MyArray + @separator
WHILE PATINDEX( @SeperatorString , @MyArray) <> 0 
BEGIN
    SELECT @separator_position =  PATINDEX(@SeperatorString , @MyArray)
    SELECT @array_value = LEFT(@MyArray, @separator_position - 1)
    INSERT @RetTable VALUES ( CAST(@array_value AS VARCHAR(256)) )
    SELECT @MyArray = STUFF(@MyArray, 1, @separator_position, '')
END

RETURN

END

If you want more explanation on this function, how the function is using and what the parameters are , you can take a look here.. This is very simple function and easy to use.

Spliting large string in Sql Server

3 Answers3