數據庫中一不定長字符串 typeString 格式為 22/11/12/13/45/123/456已知一字符串格式(不定長): 22/11/33/44/345/123求查找typeString中出現過 (22 11 33 44 345 123)中任意一個的紀錄
解决方案 »
- 这也算是一个Bug吗?
- 请问下,明细账的SQL处理,谢谢。
- 请问:像这样“001|054|033”的参数传递到存储过程中,如何一个个给分离出来?而且位数不确定,可能有多个。
- 我安装了db2为什么就是用不起!!!急!!!
- SQL2005如何修改语言设置
- 请教一个函数中IF和CASE的正确写法
- SQL Server2000 复制问题. 急求.3天了
- 有时间的朋友就来看看,太难了,不要耽误大家的时间了。
- 请问各位大虾,VFP中怎样才能做到数据安全性的完美,比如我做了个工程,发布后怎样才能使别人无法更改我表中的数据等,另外VFP做的东东怎样才能做到在网络(INTERNET和LAN)上的发布。
- 哪位朋友知道GIS中如何查询 2个站点是否有公共 汽车可达?如果没有,如何实现转车?
- select * from tb where a=xx and b=XXX这样的句子,是不是在A 和 B列上建立一个聚集组合索引才查起来最快?
- 数据库中在存储过程中做时间列的比较的问题
insert into tb select '101,102,103,107'
insert into tb select '104,105,106'
insert into tb select '102'
insert into tb select '103'if object_id('temp') is not null
drop table temp
go
declare @sql varchar(8000)
set @sql='103,101,107'
set @sql='select * into temp from (select '+replace(@sql,',',' as id union all select ')+')tp'
exec(@sql)declare @where varchar(8000)
select @where=isnull(@where+' or ','')+'charindex('''+ltrim(id)+''',s)>0'
from temp
exec('select * from tb where '+@where)101,102,103,107
103
CREATE FUNCTION StringANDString
(
@a nvarchar(2048),
@b nvarchar(2048)
)
RETURNS nvarchar(2048)
AS
begin
declare @aa nvarchar(2048)
declare @bb nvarchar(2048)
if(@a is null)
set @a = N''
if(@b is null)
set @b = N''
start_aclearspace_loop:
if(charindex(N', ',@a)=0 and charindex(N' ,',@a)=0 and charindex(N'//',@a)=0)
begin
set @a = N'//' + @a + N'//';
set @a = LTRIM(RTRIM(replace(replace(@a,N'///',N''),N'//',N'')))
goto end_aclearspace_loop;
end
else
begin
set @a = LTRIM(RTRIM(replace(replace(replace(@a,N', ',N'/'),N' ,',N'/'),N'//',N'/')))
goto start_aclearspace_loop
end
end_aclearspace_loop:
start_bclearspace_loop:
if(charindex(N', ',@b)=0 and charindex(N' ,',@b)=0 and charindex(N'//',@b)=0)
begin
set @b = N'//' + @b + N'//';
set @b = LTRIM(RTRIM(replace(replace(@b,N'///',N''),N'//',N'')))
goto end_bclearspace_loop;
end
else
begin
set @b = LTRIM(RTRIM(replace(replace(replace(@b,N', ',N'/'),N' ,',N'/'),N'//',N'/')))
goto start_bclearspace_loop
end
end_bclearspace_loop:
set @aa = @a
set @bb = @b
if(@aa is null)
set @aa = N'';
set @aa = LTRIM(RTRIM(@aa))
if(@bb is null)
set @bb = N'';
set @bb = LTRIM(RTRIM(@bb))
if(@aa='' or @bb='')
return ''
declare @cc nvarchar(2048)
set @cc = @aa + N'/' + @bb
declare @c nvarchar(2048)
set @c = N''
start_loop:
set @cc = LTRIM(RTRIM(@cc))
set @c = LTRIM(RTRIM(@c))
declare @cci int;
set @cci = charindex('/',@cc)
if @cc is not null and @cc <>''
begin
if @cci=0
begin
if(charindex(N'/'+@cc+N'/',N'/'+@c+N'/')=0 and charindex(N'/'+@cc+N'/',N'/'+@aa+N'/')<>0 and charindex(N'/'+@cc+N'/',N'/'+@bb+N'/')<>0)
begin
if(@c<>N'')
set @c = @c + N'/'
set @c = @c + @cc
end
goto end_loop
end
if @cci=1
begin
set @cc = substring(@cc,@cci+1,len(@cc)-1);
goto start_loop
end
else
begin
if(substring(@cc,1,charindex('/',@cc)-1) is not null and LTRIM(RTRIM(substring(@cc,1,charindex('/',@cc)-1)))<>N'' and charindex(N'/'+LTRIM(RTRIM(substring(@cc,1,charindex('/',@cc)-1)))+N'/',N'/'+@c+N'/')=0 and charindex(N'/'+LTRIM(RTRIM(substring(@cc,1,charindex('/',@cc)-1)))+N'/',N'/'+@aa+N'/')<>0 and charindex(N'/'+LTRIM(RTRIM(substring(@cc,1,charindex('/',@cc)-1)))+N'/',N'/'+@bb+N'/')<>0)
begin
if(@c is not null and @c<>N'')
set @c = @c + '/';
set @c = @c + RTRIM(LTRIM(substring(@cc,1,charindex('/',@cc)-1)))
end
set @cc = substring(@cc,charindex('/',@cc),len(@cc)-charindex('/',@cc)+1)
goto start_loop
end
end
end_loop:
return @c
end--dbo.StringAndString('12/13/14/15','12,122,123321,14')将返回'12/14'
--所以你只要判断
if(dbo.StringAndString(typeString ,'22/11/33/44/345/123')='')
begin
print '没有'
end
else
begin
print '有'
end
drop table tb
go
create table tb(s varchar(50))
insert into tb select '22/11/12/13/45/123/466'
insert into tb select '123/456'
insert into tb select '13'
go
if object_id('temp') is not null
drop table temp
go
declare @sql varchar(8000)
set @sql='13/45'
set @sql='select * into temp from (select '+replace(@sql,'/',' as id union all select ')+')tp'
exec(@sql)declare @where varchar(8000)
select @where=isnull(@where+' or ','')+'charindex(''/'+ltrim(id)+'/'',''/''+s+''/'')>0'
from temp
exec('select * from tb where '+@where)22/11/12/13/45/123/466
13
create table tb(id int , [values] varchar(50))
insert into tb values(1,'22/11/12/13/45/123/456 ')
insert into tb values(2,'22/11/12/13/45/123/456/AA/BB/CC/DD')
insert into tb values(3,'AA/BB/CC/DD')
go
--建立一临时表tmp1.
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b
--设置变量,并将变量放入另一临时表tmp2。
declare @sql as varchar(1000)
declare @str as varchar(100)
set @str = '22/11/33/44/345/123'
set @sql= 'select '+replace(@str,'/',' as id union all select ')
exec('select * into tmp2 from (' + @sql + ') t')select m.* from tb m where id in
(
select distinct id from
(
SELECT A.id, [values] = SUBSTRING(A.[values], B.id, CHARINDEX('/', A.[values] + '/', B.id) - B.id)
FROM tb A, tmp B
WHERE SUBSTRING('/' + A.[values], B.id, 1) = '/'
) t1
where [values] in (select cast(id as varchar) from tmp2)
)drop table tb,tmp,tmp2/*
id values
----------- --------------------------------------------------
1 22/11/12/13/45/123/456
2 22/11/12/13/45/123/456/AA/BB/CC/DD(所影响的行数为 2 行)
*/