传递给LEFT或SUBSTRING函数的长度参数无效。 [英] Invalid length parameter passed to the LEFT or SUBSTRING function.
问题描述
大家好,
这是我的分割功能。
create FUNCTION [dbo]。[Split2]
(
@String NVARCHAR ( 4000 ),
@Delimiter NCHAR ( 1 )
)
RETURNS TABLE
AS
返回
(
WITH 拆分(stpos,endpos)
AS (
SELECT 0 AS stpos,CHARINDEX( @ Delimiter , @ String ) AS endpos
UNION < span class =code-keyword> ALL
SELECT endpos + 1,CHARINDEX( @分隔符, @ String ,endpos + 1)
FROM 拆分
WHERE endpos> 0
)
SELECT ' Id' = ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 )),
' 数据' = 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 拆分
)
..我试图运行此查询
声明@ String1 nvarchar (最大);
set @ String1 ='1 $ 1,0 $ 1,2 $ 2,1 $ 2,0 $ 2,3 $ 3,1 $ 3,0 $ 3,4 $ 4,3 $ 4'
SELECT数据,UserId FROM dbo.Split2(@ String1,',')
选项(maxrecursion 0)
它工作得很好,但是当我传递超过4000个字符时我收到此错误
传递的长度参数无效 LEFT 或 SUBSTRING 功能。
然后,我试图改变
@ String NVARCHAR ( 4000 )
在分割函数中作为nvarchar( max);
然后显示
允许的最大值( 4000 ) 。
我如何实现这一点,任何想法都非常感谢。我想拆分可能超过10000或20000的字符串。
',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 拆分
)
..我尝试运行此查询
声明@ String1 nvarchar(max);
set @ String1 ='1
1,0
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.
',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
这篇关于传递给LEFT或SUBSTRING函数的长度参数无效。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!