Question

在SQL2005中运行自定义函数为什么出现“向 substring 函数传递了无效的 length 参数。”

用以下语句创建函数，取字符串中的各项值。。
======================================
--检查函数是否存在，并删除
if exists (select * from sysobjects where xtype='fn' and name='fn_checkstr')
begin
drop function fn_checkstr
end
GO

--重新创建字符串截取函数
create function fn_checkstr
(@text as nvarchar(1000),@site as int) returns nvarchar(1000)
as
begin
declare @index as int,@str as nvarchar(1000);
set @index=1;
while @index<=@site
begin
set @str=left(ltrim(@text),charindex(' ',ltrim(@text))-1)
set @text=right(ltrim(@text),len(ltrim(@text))-charindex(' ',ltrim(@text)))
end
return @str;
end
GO

====================================

用以下语句允许查询：
select dbo.fn_checkstr('2011-01-07 11:45 507 1.txt ',1)
select dbo.fn_checkstr('2011-01-07 11:45 507 1.txt ',2)
select dbo.fn_checkstr('2011-01-07 11:45 507 1.txt ',3)
select dbo.fn_checkstr('2011-01-07 11:45 507 1.txt ',4)
可以得出以下结果：
2011-01-07
11:45
507
1.txt

但问题是会报以下错误，请问是怎么回事啊？
===========================================
消息 536，级别 16，状态 2，过程 fn_checkstr，第 12 行
向 substring 函数传递了无效的 length 参数。

(1 行受影响)
============================================

Answer 1

CREATE function [dbo].[fn_checkstr]
(@text as nvarchar(1000),@site as int) returns nvarchar(1000)
as
begin
declare @index as int,@str as nvarchar(1000);
set @index=1;
while @index<=@site
begin
if charindex(' ',ltrim(@text))=0
return @text
else
set @str=left(ltrim(@text),charindex(' ',ltrim(@text))-1)
set @text=substring(ltrim(@text),charindex(' ',ltrim(@text))+1,len(ltrim(@text))-charindex(' ',ltrim(@text)))
set @index=@index+1
end
return @str;
end
出错原因在于字符最后的空格。
当index=4时，set @text=right(ltrim(@text),len(ltrim(@text))-charindex(' ',ltrim(@text)))
len(ltrim(@text))=4，charindex(' ',ltrim(@text))=5，相当于right(@text,-1)。
修改语句中用了substring，去除最后空格的影响。

Answer 2

1.死循环
2.当@str='111' 时，left第二个参数会出现负数
left(ltrim(@text),charindex(' ',ltrim(@text))-1)--此处