22,209
社区成员
发帖
与我相关
我的任务
分享
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'
use PracticeDB
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter function fn_serch
(
@route varchar(100)
)returns
@temp table(编号 varchar(100),公交路线 varchar(100))
as
begin
;with t
as
(
select a.编号, SUBSTRING(a.公交路线,number,CHARINDEX(',',a.公交路线+',',number)-b.number) col
from tb a join master..spt_values b on b.type='p'
and CHARINDEX(',',','+a.公交路线,number)=number
and number between 1 and len(a.公交路线)
)
insert into @temp
select tb.*
from tb join t on t.编号=tb.编号
where t.col=@route
return
end
go
select * from fn_serch('10')
编号 公交路线
10002 10,110
编号: 公交路线,序号
10001 1 1
10001 101 2
10002 10 1
10002 110 2
select * from tb where charindex(',10,' , ',' + 公交路线 + ',') > 0
select * from tb where ',' + 公交路线 + ',' like '%,10,%'
declare @s varchar(10)
set @s='10'
select * from tb where charindex(','+@s+',',','+公交路线+',')>0