--列车车次信息数据表
CREATE TABLE tb(col varchar(100))
INSERT tb SELECT '1434/1/2/14'
UNION ALL SELECT '"10653(85707)"'
UNION ALL SELECT '"32608/7(83212/1)"'
UNION ALL SELECT '"50057()"'
UNION ALL SELECT '"T888(备)"'
UNION ALL SELECT '"21058(81404/3)0"'
UNION ALL SELECT '"22028(80404.10264)"'
UNION ALL SELECT '20037(80303.84006/9)'
UNION ALL SELECT '24031(80410/9'
UNION ALL SELECT '24048(80904)(23118)'
UNION ALL SELECT '22080(80406.83080.10284)'
UNION ALL SELECT '0031(5632 5629. 1434/1/2/14)'
CREATE FUNCTION dbo.f_CompSTR(
@str varchar(8000), --包含车次的字符串
@find varchar(50) --要查询的值
)RETURNS bit
AS
BEGIN
IF @str=@find RETURN(1)
IF LEN(@str)<LEN(@find) RETURN(0)
--统计数据分隔符
SELECT @str=REPLACE(@str,a,b)
FROM(
SELECT a='(',b='\' UNION ALL
SELECT a=')',b='\' UNION ALL
SELECT a='(',b='\' UNION ALL
SELECT a=')',b='\' UNION ALL
SELECT a=' ',b='\' UNION ALL
SELECT a=' ',b='\' UNION ALL
SELECT a='.',b='\' UNION ALL
SELECT a='.',b='\')a
--分拆比较处理
DECLARE @s1 varchar(8000),@h varchar(100),@s varchar(100),@l int
WHILE @str>''
BEGIN
SELECT @s1=LEFT(@str,CHARINDEX('\',@str+'\')-1),
@str=STUFF(@str,1,CHARINDEX('\',@str+'\'),''),
@h=LEFT(@s1,CHARINDEX('/',@s1+'/')-1),
@l=LEN(@h)+1
IF @h=@find RETURN(1)
WHILE CHARINDEX('/',@s1+'/')>0
BEGIN
SELECT @s=LEFT(@s1,CHARINDEX('/',@s1+'/')-1),
@s1=STUFF(@s1,1,CHARINDEX('/',@s1+'/'),'')
IF LEN(@s)<@l
SET @s=STUFF(@h,@l-LEN(@s),8000,@s)
IF @find=CASE
WHEN LEN(@s)<@l THEN STUFF(@h,@l-LEN(@s),8000,@s)
ELSE @S END
RETURN(1)
END
END
RETURN(0)
END
GO
--调用上述函数查询包含车次1434的记录
SELECT * FROM tb WHERE dbo. f_CompSTR(col,’143’)=1
--最短乘车路线查询示例(邹老大的。)
CREATE TABLE T_Line(
ID nvarchar(10), --公交线路号
Station nvarchar(10), --站点名称
Orders int) --行车方向(通过它反应每个站的上一个、下一个站)
INSERT T_Line
SELECT N'8路' ,N'站A',1 UNION ALL
SELECT N'8路' ,N'站B',2 UNION ALL
SELECT N'8路' ,N'站C',3 UNION ALL
SELECT N'8路' ,N'站D',4 UNION ALL
SELECT N'8路' ,N'站J',5 UNION ALL
SELECT N'8路' ,N'站L',6 UNION ALL
SELECT N'8路' ,N'站M',7 UNION ALL
SELECT N'20路' ,N'站G',1 UNION ALL
SELECT N'20路' ,N'站H',2 UNION ALL
SELECT N'20路' ,N'站I',3 UNION ALL
SELECT N'20路' ,N'站J',4 UNION ALL
SELECT N'20路' ,N'站L',5 UNION ALL
SELECT N'20路' ,N'站M',6 UNION ALL
SELECT N'255路',N'站N',1 UNION ALL
SELECT N'255路',N'站O',2 UNION ALL
SELECT N'255路',N'站P',3 UNION ALL
SELECT N'255路',N'站Q',4 UNION ALL
SELECT N'255路',N'站J',5 UNION ALL
SELECT N'255路',N'站D',6 UNION ALL
SELECT N'255路',N'站E',7 UNION ALL
SELECT N'255路',N'站F',8
GO
--乘车线路查询存储过程
CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT ID,Station,
Line=CAST('('+RTRIM(ID)+': '+RTRIM(Station) as nvarchar(4000)),
Orders=Orders,
[Level]=@l
INTO # FROM T_Line
WHERE Station=@Station_Start
WHILE @@ROWCOUNT>0
AND NOT EXISTS(SELECT * FROM # WHERE Station=@Station_Stop)
BEGIN
SET @l=@l+1
INSERT #(Line,ID,Station,Orders,[Level])
SELECT
Line=a.Line+CASE
WHEN a.ID=b.ID THEN N'->'+RTRIM(b.Station)
ELSE N') ∝ ('+RTRIM(b.ID)
+N': '+RTRIM(b.Station) END,
b.ID,b.Station,b.Orders,@l
FROM # a,T_Line b
WHERE a.[Level]=@l-1
AND(a.Station=b.Station AND a.ID<>b.ID
OR a.ID=b.ID AND(
a.Orders=b.Orders+1
OR
a.Orders=b.Orders-1))
AND LEN(a.Line)<4000
AND PATINDEX('%[ >]'+b.Station+'[-)]%',a.Line)=0
END
SELECT N'起点站'=@Station_Start
,N'终点站'=@Station_Stop
,N'乘车线路'=Line+N')'
FROM #
WHERE [Level]=@l
AND Station=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查
SELECT * FROM #
GO