SQL语句问题，有挑战性。（高分回报）
declare @aaa varchar(20),@bbb varchar(20)
set @aaa="2,3,4,6"
set @bbb="1,2,3,4,5,6,7,8"
我想用SQL语句来判断：@aaa 中至少有一个元素在 @bbb 中出现，怎么写？？
请高手帮忙，另开贴给分。
问题点数：20、回复次数：7Top
1 楼ping3000（苦练葵花点穴手）回复于 2006-02-25 16:06:44 得分 5
declare @aaa nvarchar(50)
declare @bbb nvarchar(100)
declare @c nvarchar(5)
declare @return nvarchar(5)
set @aaa='2,3,4,6'
set @bbb='1,2,3,4,5,6,7,8'
DECLARE @start INT,@end INT
SET @start = 1
SET @end = CHARINDEX(',', @aaa, @start)
IF @end = 0 BEGIN SET @end = LEN(@aaa) + 1 END
WHILE(@end > @start)
BEGIN
SET @c = substring(@aaa,@start,1)
IF CHARINDEX(@c,@bbb,1)>0
BEGIN
SET @return = '有'
SELECT @return
RETURN
END
SET @start = @end + 1
SET @end = CHARINDEX(',', @aaa, @start)
IF @end = 0
BEGIN
SET @end = LEN(@aaa) + 1
END
ENDTop
2 楼wangdehao（找找找(现在很幸福)）回复于 2006-02-25 16:21:44 得分 5
---创建函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-1
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen-1,'')
END
INSERT @re VALUES(@s)
RETURN
END
-----调用
declare @aaa varchar(20),@bbb varchar(20)
set @aaa='2,3,4,6'
set @bbb='1,2,3,4,5,6,7,8'
select * from f_splitstr(@aaa,',') where charindex(col,@bbb)>0
-----返回结果:：@aaa 在 @bbb中出现的元素列表
col
2
3
4
6
Top
3 楼ping3000（苦练葵花点穴手）回复于 2006-02-25 16:23:27 得分 0
刚才的改正一下,如果有不都是个位数
declare @aaa nvarchar(50)
declare @bbb nvarchar(100)
declare @c nvarchar(5)
declare @return nvarchar(5)
set @aaa='11,12,9,22'
set @bbb='1,2,3,4,5,6,7,8'
set @return = '没有'
DECLARE @start INT,@end INT
SET @start = 1
SET @end = CHARINDEX(',', @aaa, @start)
IF @end = 0 BEGIN SET @end = LEN(@aaa) + 1 END
WHILE(@end > @start)
BEGIN
SET @c = substring(@aaa,@start,@end -@start)
IF CHARINDEX(@c,@bbb,1)>0
BEGIN
SET @return = '有'
RETURN
END
SET @start = @end + 1
SET @end = CHARINDEX(',', @aaa, @start)
IF @end = 0
BEGIN
SET @end = LEN(@aaa) + 1
END
END
SELECT @returnTop
4 楼wangdehao（找找找(现在很幸福)）回复于 2006-02-25 16:31:55 得分 0
-----调用(严谨点)
declare @aaa varchar(20),@bbb varchar(20)
set @aaa='2,3,4,6'
set @bbb='1,2,3,4,5,6,7,8'
select * from f_splitstr(@aaa,',') where charindex(','+col+',',','+@bbb+',')>0Top
5 楼huchuan_1984（渴求）回复于 2006-02-25 17:47:20 得分 5
DECLARE @cString1 VARCHAR(100)
DECLARE @cString2 VARCHAR(100)
DECLARE @cString3 VARCHAR(1100)
DECLARE @cString4 VARCHAR(1000)
SET @cString1 = '1,SDF,O,D'
SET @cString2 = '1,2,3,4,5,6,7,8,9'
DECLARE @iStart INT
DECLARE @iLen INT
SET @cString4 = @cString1
WHILE LEN(@cString1)>0 BEGIN
SET @iStart=CHARINDEX(',',@cString1)
IF @iStart=0 BEGIN
SET @cString3=LTRIM(@cString1)
SET @cString1=''
END
ELSE BEGIN
SET @iLen=LEN(@cString1)
SET @cString3=LEFT(@cString1,@iStart-1)
SET @cString1=LTRIM(RIGHT(@cString1,@iLen-@iStart))
END
IF CHARINDEX(@cString3 , @cString2 ) = 0
PRINT @cString4 + '不在' + @cString2
ELSE
PRINT @cString4 + '在' + @cString2
END
Top
6 楼scmail81（琳·风の狼(修罗)）回复于 2006-02-25 21:10:41 得分 3
declare @aaa varchar(20),@bbb varchar(20)
set @aaa='2,3,4,6'
set @bbb='1,2,3,4,5,6,7,8'
declare @T varchar(20)
declare @Flag bit
set @Flag=0
if right(@aaa,1)=','
set @T=@aaa
else
set @T=@aaa+','
while charindex(',',@T)>0
begin
if charindex(left(@T,charindex(',',@T)-1) ,@bbb)>0
begin
set @Flag=1
set @T=''
end
else
set @T= right(@T,len(@T)-charindex(',',@T))
end
if @Flag=1
print '存在'
else
print '不存在'Top
7 楼wumingxiaodi（无名小弟）回复于 2006-02-26 21:24:06 得分 2
declare @aaa varchar(20),@bbb varchar(20)
set @aaa='2,3,4,6'
set @bbb='1,2,3,4,5,6,7,8'
declare @sub varchar(30)
while(len(@aaa)>0)/*逐个截取@aaa中字符*/
begin
if(charindex(',',@aaa)>=1)
begin
set @sub = left(@aaa,charindex(',',@aaa)-1)/*从最左边开始截取*/
/*将@aaa赋值为去掉左边第一个字符和逗号的部分*/
set @aaa = right(@aaa,len(@aaa)-charindex(',',@aaa))
end
else
begin
set @sub = @aaa /*没有逗号表示只有一个字符或没有,直接赋值*/
set @aaa=''
end
if(@sub=',')
continue
if(charindex(@sub,@bbb)>0)/*判断是否存在,存在则返回结果,跳出循环*/
begin
select '存在'
return
end
end
select '不存在'
Top
