如何将sql server表中逗号分隔的列值与所有组合中的逗号分隔值进行比较 [英] How to compare comma separated column values in sql server table with comma separated value in all combination

问题描述

在SQL Server中，如何将表格列中逗号分隔的字符串值与逗号分隔的参数值进行比较

In SQL Server how can i compare comma separated string values in a table column with parameter value as comma separated

Product Dimension
1       1,3,5
2       1,3,7

sql存储过程参数我将传递为1,3,5或1,5,3或任何提到的数字组合的顺序



i需要检查表中是否传递了值？

sql stored procedure parameter i'll pass as 1,3,5 or 1,5,3 or any order of mentioned numbers combination

i need to check whether passed value available in table or not?

推荐答案

请阅读我对该问题的评论。



如果你想比较尺寸，你需要的只是：

Please, read my comment to the question.

If youo want to compare Dimensions, all you need to to is:

1. 解析每个产品的尺寸
   
   
   
   < TD> 1

   产品	 	ValueOfDimension
   1	 
   1	 	3
   1	 	5
   2	 	1
   2	 	3
   2		7

   
   
   
2. 解析维度来自字符串变量
3. 比较它们

1. parse Dimension for each product
   
   

   Product		ValueOfDimension
   1		1
   1		3
   1		5
   2		1
   2		3
   2		7

   
   
2. parse Dimension from string variable
3. compare them

如果您提供更多详细信息，我保证会更新我的答案。





看看这里：

If you provide more details, i'll promise to update my answer.


Have a look here:

DECLARE @tmp TABLE (Product INT IDENTITY(1,1), Dimension VARCHAR(30))

INSERT INTO @tmp (Dimension)
VALUES('1,3,5'),
('1,3,7')

DECLARE @input VARCHAR(30) = '1,5,3'

;WITH FirstCTE AS
(
	SELECT Product, CONVERT(INT, LEFT(Dimension, CHARINDEX(',', Dimension)-1)) AS MyValue, RIGHT(Dimension , LEN(Dimension) - CHARINDEX(',', Dimension)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', Dimension)>0
	UNION ALL
	SELECT Product, CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS MyValue, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM FirstCTE
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Product, CONVERT(INT, Remainder) AS MyValue, NULL AS Remainder
	FROM FirstCTE
	WHERE CHARINDEX(',', Remainder)=0
),
SecondCTE AS
(
	SELECT CONVERT(INT, LEFT(@input, CHARINDEX(',', @input)-1)) AS MyValue, RIGHT(@input, LEN(@input) - CHARINDEX(',', @input)) AS Remainder
	WHERE CHARINDEX(',', @input)>0
	UNION ALL
	SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS MyValue, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM SecondCTE
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT CONVERT(INT, Remainder) AS MyValue, NULL AS Remainder
	FROM SecondCTE
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT t1.Product, t1.MyValue AS MyValue1, t1.Remainder AS Remainder1, t2.MyValue AS MyValue2, t2.Remainder AS Remainder2 
FROM FirstCTE AS t1 INNER JOIN  SecondCTE AS t2 ON t1.MyValue = t2.MyValue 

如你所见，我使用 CTE [ ^ ]。

[/ EDIT]

As you can see, i used CTE[^].
[/EDIT]

这篇关于如何将sql server表中逗号分隔的列值与所有组合中的逗号分隔值进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！