select * from yhzc a inner join jps b on len(a.qw)-len(replace(a.qw,b.jm,''))>0
哦,两个表 select a.* from yhzc a inner join jps b on charindex(b.jm,a.qw)>0
--建立测试环境 Create table YHZC (QW Nvarchar(100)) Create table JPS (JM Nvarchar(10)) GO --插入数据 Insert YHZC Values(N'耿75|欢2-11-5216|欢2-12-206|欢612-平1领眼|锦2-15-新221井|') Insert JPS Values(N'耿75') GO --测试 Select * from JPS Inner Join YHZC On CharIndex(JM,QW)>0 --删除测试环境 Drop table YHZC Drop table JPS --结果 /* JM QW 耿75 耿75|欢2-11-5216|欢2-12-206|欢612-平1领眼|锦2-15-新221井| */
select a.*, b.* from yhzc a inner join jps b on charindex('|'+b.jm+'|','|'+a.qw+'|')>0
-- 函数: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 a.* from yhzc a inner join jps b on charindex(b.jm,a.qw+'|')>0 这样才准确些
select a.* from yhzc a inner join jps b on charindex(b.jm+'|',a.qw)>0 更正一下
from yhzc a
inner join jps b
on len(a.qw)-len(replace(a.qw,b.jm,''))>0
select a.* from yhzc a inner join jps b on charindex(b.jm,a.qw)>0
--建立测试环境
Create table YHZC
(QW Nvarchar(100))
Create table JPS
(JM Nvarchar(10))
GO
--插入数据
Insert YHZC Values(N'耿75|欢2-11-5216|欢2-12-206|欢612-平1领眼|锦2-15-新221井|')
Insert JPS Values(N'耿75')
GO
--测试
Select * from JPS Inner Join YHZC On CharIndex(JM,QW)>0
--删除测试环境
Drop table YHZC
Drop table JPS
--结果
/*
JM QW
耿75 耿75|欢2-11-5216|欢2-12-206|欢612-平1领眼|锦2-15-新221井|
*/
a.*,
b.*
from
yhzc a
inner join
jps b
on
charindex('|'+b.jm+'|','|'+a.qw+'|')>0
-- 作者:刘寰
-- 邮件:[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
这样才准确些
更正一下