-- 函数:InArray -- 作者:刘寰 -- 邮件:[email protected] -- 日期:2005-03-27 -- 功能:比较两个字符串按某个分隔符截分后,是否存在完全包含关系IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[InArray]') and xtype in (N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[InArray] GO CREATE FUNCTION InArray (@SQLStr1 varchar(8000), --第一个字符串或SQL查询结果 @SplitChar1 varchar(800), --每一个字符串的分隔符 @SQLStr2 varchar(8000), --第二个字符串或SQL查询结果 @SplitChar2 varchar(800), --第二个字符串的分隔符 @Type bit) --0代表第一个字符串截分后的所有字符都在第二字符串截分后的集合中,1代表反向情况RETURNS bit --0代表结果为非完全包含,1代表结果为完全包含 BEGIN DECLARE @i1 int,@SplitCharLEN1 int,@Str1 varchar(8000) DECLARE @i2 int,@SplitCharLEN2 int,@Str2 varchar(8000) DECLARE @j1 int,@j2 int,@value bit DECLARE @List1 table(val varchar(8000)) DECLARE @List2 table(val varchar(8000)) IF @SplitChar1=' ' SET @SplitCharLEN1=LEN(REPLACE(@SplitChar1,' ','R')) ELSE SET @SplitCharLEN1=LEN(@SplitChar1) IF @SplitChar2=' ' SET @SplitCharLEN2=LEN(REPLACE(@SplitChar2,' ','R')) ELSE SET @SplitCharLEN2=LEN(@SplitChar2) SET @SQLStr1=@SQLStr1+@SplitChar1 SET @i1=CHARINDEX(@SplitChar1,@SQLStr1) SET @SQLStr2=@SQLStr2+@SplitChar2 SET @i2=CHARINDEX(@SplitChar2,@SQLStr2) WHILE @i1>0 BEGIN SET @Str1=SUBSTRING(@SQLStr1,1,@i1-1) SET @SQLStr1=SUBSTRING(@SQLStr1,LEN(@Str1)+@SplitCharLEN1+1,LEN(@SQLStr1)) SET @i1=CHARINDEX(@SplitChar1,@SQLStr1) IF (LEN(@Str1)!=0) INSERT INTO @List1(val) SELECT @Str1 END WHILE @i2>0 BEGIN SET @Str2=SUBSTRING(@SQLStr2,1,@i2-1) SET @SQLStr2=SUBSTRING(@SQLStr2,LEN(@Str2)+@SplitCharLEN2+1,LEN(@SQLStr2)) SET @i2=CHARINDEX(@SplitChar2,@SQLStr2) IF (LEN(@Str2)!=0) INSERT INTO @List2(val) SELECT @Str2 END IF @Type=0 BEGIN SELECT @j1=(SELECT COUNT(*) FROM (SELECT * FROM @List1) a,(SELECT * FROM @List2) b WHERE a.val=b.val),@j2=(SELECT COUNT(*) FROM @List1) SELECT @value=(CASE WHEN @j1=@j2 THEN 1 ELSE 0 END) END ELSE BEGIN SELECT @j1=(SELECT COUNT(*) FROM (SELECT * FROM @List1) a,(SELECT * FROM @List2) b WHERE a.val=b.val),@j2=(SELECT COUNT(*) FROM @List2) SELECT @value=(CASE WHEN @j1=@j2 THEN 1 ELSE 0 END) END RETURN @value END GO -- 测试数据 -- CREATE TABLE #InArrayTable (TestField varchar(8000)) INSERT INTO #InArrayTable VALUES ('1') INSERT INTO #InArrayTable VALUES ('1,2') INSERT INTO #InArrayTable VALUES ('1,2,3') INSERT INTO #InArrayTable VALUES ('1,2,3,4') INSERT INTO #InArrayTable VALUES ('1,2,3,4,5') INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6') INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7') INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7,8') INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7,8,9') INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7,8,9,10') INSERT INTO #InArrayTable VALUES ('A') INSERT INTO #InArrayTable VALUES (',A,B,') INSERT INTO #InArrayTable VALUES (',C,D,E,F,') INSERT INTO #InArrayTable VALUES (',A,B,C,') INSERT INTO #InArrayTable VALUES (',,A,,B,,C,,')-- 测试结果 -- SELECT * FROM #InArrayTable WHERE dbo.InArray ('5,6',',',TestField,',',0)=1 /* TestField --------------------- 1,2,3,4,5,6 1,2,3,4,5,6,7 1,2,3,4,5,6,7,8 1,2,3,4,5,6,7,8,9 1,2,3,4,5,6,7,8,9,10(所影响的行数为 5 行) */ SELECT * FROM #InArrayTable WHERE dbo.InArray (' B C D E F ',' ',TestField,',',1)=1 /* TestField --------------------- ,C,D,E,F,(所影响的行数为 1 行) */ -- 删除测试数据 -- DROP TABLE #InArrayTable
select *
from tablename
where charindex(N'欢2-12-206',colname)>0
select *
from tablename
where len(conname)-len(replace(N'欢2-12-206',colname,''))>0
if charindex(N'欢2-12-206',colname)>0 --len(conname)-len(replace(N'欢2-12-206',colname,''))>0
print '属于'
if charindex(N'欢2-12-206',colname)<=0 --len(conname)-len(replace(N'欢2-12-206',colname,''))<=0
print '不属于'
-- 作者:刘寰
-- 邮件:[email protected]
-- 日期:2005-03-27
-- 功能:比较两个字符串按某个分隔符截分后,是否存在完全包含关系IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[InArray]') and xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[InArray]
GO
CREATE FUNCTION InArray
(@SQLStr1 varchar(8000), --第一个字符串或SQL查询结果
@SplitChar1 varchar(800), --每一个字符串的分隔符
@SQLStr2 varchar(8000), --第二个字符串或SQL查询结果
@SplitChar2 varchar(800), --第二个字符串的分隔符
@Type bit) --0代表第一个字符串截分后的所有字符都在第二字符串截分后的集合中,1代表反向情况RETURNS bit --0代表结果为非完全包含,1代表结果为完全包含
BEGIN
DECLARE @i1 int,@SplitCharLEN1 int,@Str1 varchar(8000)
DECLARE @i2 int,@SplitCharLEN2 int,@Str2 varchar(8000)
DECLARE @j1 int,@j2 int,@value bit DECLARE @List1 table(val varchar(8000))
DECLARE @List2 table(val varchar(8000)) IF @SplitChar1=' '
SET @SplitCharLEN1=LEN(REPLACE(@SplitChar1,' ','R'))
ELSE
SET @SplitCharLEN1=LEN(@SplitChar1)
IF @SplitChar2=' '
SET @SplitCharLEN2=LEN(REPLACE(@SplitChar2,' ','R'))
ELSE
SET @SplitCharLEN2=LEN(@SplitChar2) SET @SQLStr1=@SQLStr1+@SplitChar1
SET @i1=CHARINDEX(@SplitChar1,@SQLStr1)
SET @SQLStr2=@SQLStr2+@SplitChar2
SET @i2=CHARINDEX(@SplitChar2,@SQLStr2) WHILE @i1>0
BEGIN
SET @Str1=SUBSTRING(@SQLStr1,1,@i1-1)
SET @SQLStr1=SUBSTRING(@SQLStr1,LEN(@Str1)+@SplitCharLEN1+1,LEN(@SQLStr1))
SET @i1=CHARINDEX(@SplitChar1,@SQLStr1)
IF (LEN(@Str1)!=0)
INSERT INTO @List1(val) SELECT @Str1
END WHILE @i2>0
BEGIN
SET @Str2=SUBSTRING(@SQLStr2,1,@i2-1)
SET @SQLStr2=SUBSTRING(@SQLStr2,LEN(@Str2)+@SplitCharLEN2+1,LEN(@SQLStr2))
SET @i2=CHARINDEX(@SplitChar2,@SQLStr2)
IF (LEN(@Str2)!=0)
INSERT INTO @List2(val) SELECT @Str2
END IF @Type=0
BEGIN
SELECT @j1=(SELECT COUNT(*) FROM (SELECT * FROM @List1) a,(SELECT * FROM @List2) b WHERE a.val=b.val),@j2=(SELECT COUNT(*) FROM @List1)
SELECT @value=(CASE WHEN @j1=@j2 THEN 1 ELSE 0 END)
END
ELSE
BEGIN
SELECT @j1=(SELECT COUNT(*) FROM (SELECT * FROM @List1) a,(SELECT * FROM @List2) b WHERE a.val=b.val),@j2=(SELECT COUNT(*) FROM @List2)
SELECT @value=(CASE WHEN @j1=@j2 THEN 1 ELSE 0 END)
END
RETURN @value
END
GO
-- 测试数据 --
CREATE TABLE #InArrayTable (TestField varchar(8000))
INSERT INTO #InArrayTable VALUES ('1')
INSERT INTO #InArrayTable VALUES ('1,2')
INSERT INTO #InArrayTable VALUES ('1,2,3')
INSERT INTO #InArrayTable VALUES ('1,2,3,4')
INSERT INTO #InArrayTable VALUES ('1,2,3,4,5')
INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6')
INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7')
INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7,8')
INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7,8,9')
INSERT INTO #InArrayTable VALUES ('1,2,3,4,5,6,7,8,9,10')
INSERT INTO #InArrayTable VALUES ('A')
INSERT INTO #InArrayTable VALUES (',A,B,')
INSERT INTO #InArrayTable VALUES (',C,D,E,F,')
INSERT INTO #InArrayTable VALUES (',A,B,C,')
INSERT INTO #InArrayTable VALUES (',,A,,B,,C,,')-- 测试结果 --
SELECT * FROM #InArrayTable WHERE dbo.InArray ('5,6',',',TestField,',',0)=1
/*
TestField
---------------------
1,2,3,4,5,6
1,2,3,4,5,6,7
1,2,3,4,5,6,7,8
1,2,3,4,5,6,7,8,9
1,2,3,4,5,6,7,8,9,10(所影响的行数为 5 行)
*/
SELECT * FROM #InArrayTable WHERE dbo.InArray (' B C D E F ',' ',TestField,',',1)=1
/*
TestField
---------------------
,C,D,E,F,(所影响的行数为 1 行)
*/
-- 删除测试数据 --
DROP TABLE #InArrayTable