您的位置: 首页  >  技术问答  >  如何将sql server表中逗号分隔的列值与所有组合中的逗号分隔值进行比较

如何将sql server表中逗号分隔的列值与所有组合中的逗号分隔值进行比较

分类: 技术问答 • 2022-04-23 21:24:02
问题描述：

在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. 解析每个产品的尺寸



    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]


相关推荐