问一个一个字符串检索问题
表
ID IndexArr
1 1,2,3,4,5
2 55,6,99,87,1000
3 7,567567,567,43,123
IndexArr的值是int数值join来的
现在有字符串 '2,34,45,345,867,4,984' 需要查询 这个字符串中的数字 有没有在表中出现过
sql语句怎么写?
[解决办法]
DECLARE @c VARCHAR(100)
SET @c='2,34,45,345,867,4,984'
;
WITH a1 (ID,IndexArr) AS
(
SELECT 1,'1,2,3,4,5' UNION all
SELECT 2,'55,6,99,87,1000' UNION all
SELECT 3,'7,567567,567,43,123'
)
,a2 AS
(
SELECT a.id,b.IndexArr
FROM
(
select id, IndexArr = convert(xml,' <root> <v>' + replace(IndexArr, ',', ' </v> <v>') + ' </v> </root>') from a1
)A
outer APPLY
(
select IndexArr = N.v.value('.', 'varchar(100)') from A.IndexArr.nodes('/root/v') N(v)
)B
)
SELECT a.*,b.IndexArr AS IndexArr_r
FROM a2 a
INNER JOIN a1 b on a.id=b.id
WHERE CHARINDEX(','+RTRIM(a.IndexArr)+',',','+@c+',')>0
[解决办法]
create table ay
(ID int, IndexArr varchar(50))
insert into ay
select 1, '1,2,3,4,5' union all
select 2, '55,6,99,87,1000' union all
select 3, '7,567567,567,43,123'
declare @x varchar(50)
select @x='2,34,45,345,867,4,984'
select * from ay
where exists(
select 1
from (select ','+IndexArr+',' 'x') c
cross join
(select ','+substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number)+',' 's'
from (select @x 's') a, master..spt_values b
where b.[type]='P' and b.number between 1 and len(a.s) and substring(','+a.s,b.number,1)=',') d
where charindex(d.s,c.x,1)>0)
/*
ID IndexArr
----------- --------------------------------------------------
1 1,2,3,4,5
(1 row(s) affected)
*/
----------------测试数据--------------------------
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GO
CREATE TABLE Ta
(
ID INT,
IndexArr VARCHAR(100)
);
INSERT Ta
SELECT 1,'1,2,3,4,5' UNION
SELECT 2,'55,6,99,87,1000' UNION
SELECT 3,'7,567567,567,43,123'
IF OBJECT_ID('Tb') IS NOT NULL
DROP TABLE Tb
GO
CREATE TABLE Tb
(
IndexArr VARCHAR(100)
);
INSERT Tb
SELECT '2,34,45,345,867,4,984'
-----------------End--------------------------------
WITH a AS
(
SELECT a.ID,IndexArr,IndexNo=SUBSTRING(a.IndexArr,b.Number,Charindex(',',a.IndexArr+',',b.Number)-b.Number)
FROM Ta a join master.dbo.spt_values b
on b.Number<=len(IndexArr)
Where SUBSTRING(','+a.IndexArr,b.Number,1)=',' and b.Type='P'
)
,b AS
(
SELECT IndexNo=SUBSTRING(a.IndexArr,b.Number,Charindex(',',a.IndexArr+',',b.Number)-b.Number)
FROM Tb a join master.dbo.spt_values b
on b.Number<=len(IndexArr)
Where SUBSTRING(','+a.IndexArr,b.Number,1)=',' and b.Type='P'
)
SELECT DISTINCT a.ID,a.IndexArr
FROM a WHERE EXISTS (SELECT 1 FROM b WHERE a.IndexNo=b.IndexNo)
/*
IDIndexArr
---------------------
11,2,3,4,5
(1 行受影响)
*/
