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
END
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
END
@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
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 @return
-----调用(严谨点)
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
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
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))
endif @Flag=1
print '存在'
else
print '不存在'
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 '不存在'