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 函数的长度参数无效。
