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.