Invalid length parameter passed to the LEFT or SUBSTRING function.

See more:

SQL-Server-2008R2

Hi guys,

This is a split function i have.

create FUNCTION [dbo].[Split2]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
 

RETURN 
(
    
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' =  SUBSTRING(SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos),1,CHARINDEX('$',SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos))-1),
                'UserId' =  SUBSTRING(SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos),CHARINDEX('$',SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos))+1,LEN(SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)))
 
    FROM Split
   
)

..and i tried to run this query

Declare @String1 nvarchar(max);
set @String1='1$1,0$1,2$2,1$2,0$2,3$3,1$3,0$3,4$4,3$4'
SELECT data,UserId FROM dbo.Split2(@String1, ',')
option (maxrecursion 0)


Its working perfectly, but I'm getting this error when i pass more then 4000 characters

Invalid length parameter passed to the LEFT or SUBSTRING function.

then ,I tried to change

@String NVARCHAR(4000)

in split function as nvarchar(max);
then it shows

the maximum allowed (4000).

How do I acheive this, any Idea is highly appreciated. I wanna split string which may be more than 10000 or 20000.