逗号在sql中搜索单列? [英] comma search for single column in sql ?
本文介绍了逗号在sql中搜索单列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从下表中选择数据,其中我的参数是@ Subcatid = 1,2,32
ID Subcatid ContributorID LocationId
1 1 2 379
2 2 2 379
3 4 3 200
4 32 2 45
5 41 2 379
6 45 2 30
7 4 412 45
8 5 412 379
9 41 408 379
10 45 408 NULL
解决方案
< blockquote>这是一种方法,但可能不是最好的
DECLARE @ Subcatid AS VARCHAR ( 100 )= ' 2,3,4'
EXEC (' select * from table subcatid in(' + @ Subcatid + ' )' )
你可以使用IN运算符。
从tableName中选择* where subcatid in(@ subcatid)
创建 表 main(ID int ,Subcatid int ,ContributorID int ,LocationId int )
insert 进入 main 值( 1 , 1 , 2 , 379 ),( 2 , 2 , 2 , 379 ),( 3 , 4 , 3 , 200 ),( 4 , 32 , 2 , 379 )
DECLARE @ Subcatid VARCHAR ( 50 ) = ' 1,2,32'
DECLARE @ myXML AS XML = N ' < h>< r>' + REPLACE( @ Subcatid ,' ,',' < / r>< r>')+ ' < / r> < / h>'
Se lect * 来自 main 其中 Subcatid 在(
SELECT Vals.id.value(' 。',' INT') AS val
FROM @ myXML .nodes(' / H / r') AS Vals(id )
)
drop table main
i want to select the data from below table where my parameters are @Subcatid=1,2,32
ID Subcatid ContributorID LocationId
1 1 2 379
2 2 2 379
3 4 3 200
4 32 2 45
5 41 2 379
6 45 2 30
7 4 412 45
8 5 412 379
9 41 408 379
10 45 408 NULL
解决方案
This is one way, but may not be the best
DECLARE @Subcatid AS VARCHAR(100) = '2,3,4' EXEC ('select * from table where Subcatid in ('+ @Subcatid +')')
You can use IN operator.
Select * from tableName where subcatid in (@Subcatid)
create table main (ID int, Subcatid int, ContributorID int, LocationId int) insert into main values (1,1,2,379),(2,2,2,379),(3,4,3,200),(4,32,2,379) DECLARE @Subcatid VARCHAR(50)='1,2,32' DECLARE @myXML AS XML = N'<h><r>' + REPLACE(@Subcatid, ',', '</r><r>') + '</r></h>' Select * from main where Subcatid In ( SELECT Vals.id.value('.', 'INT') AS val FROM @myXML.nodes('/H/r') AS Vals(id) ) drop table main
这篇关于逗号在sql中搜索单列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文