高手帮我看看 为什么会报这个错误

[jaydom]

create table ta ( num varchar(10) )
insert into ta
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 10 union all
select 110 union all
select 101

create table tb (编号 varchar(10),公交路线 varchar(100))
insert into tb
select '10001', '1,101' union all
select '10002', '10,110'


  ;with t
    as
    ( 
       select  a.编号, cast(SUBSTRING(a.公交路线,number,CHARINDEX(',',a.公交路线+',',number)-b.number) as varchar(100)) col
       from tb a join master..spt_values b on b.type='p' and  CHARINDEX(',',','+a.公交路线,number)=number
    )
    select * 
    from  tb join t on t.编号=tb.编号
    where t.col='10'

消息 537，级别 16，状态 2，第 1 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

[jaydom]

顶一下 怎么没人来啊

[无心雨云]

没看到有[number]这个字段啊

[永生天地]

;with t
as
(
select a.编号, cast(SUBSTRING(a.公交路线,number,CHARINDEX(',',a.公交路线+',',number)-b.number) as varchar(100)) col
from tb a join master..spt_values b on b.type='p' and CHARINDEX(',',','+a.公交路线,number)=number and b.number between 1 and len(a.公交路线)
)
select *
from tb join t on t.编号=tb.编号
where t.col='10'

[无心雨云]

number is null
未处理吧

[无心雨云]

没注意，连的是系统表

[--小F--]

传递给 LEFT 或 SUBSTRING 函数的长度参数无效 检查下SUBSTRING函数中的长度是不是数值

[jaydom]

谢谢各位sf ，问题解决了 结贴