我如何检查varchar是否包含来自另一个varchar的所有字符,其中字符序列是无关紧要的？
例如：我在表’Table’中有varchar @a =’ABC’和列’Col’在哪里是’Col’=’CBAD’的行.我想选择这个行,因为它包含@a变量中的所有字符.请你帮忙
我尝试过这样的事情：
DECLARE @a varchar(5) = 'ABCD'
DECLARE @b varchar(5) = 'DCA'
DECLARE @i int = 0
DECLARE @pat varchar(30) = ''
while @i <> len(@b) BEGIN
SET @i = @i + 1
SET @pat = @pat + '[' + @a + ']'
END
SELECT @pat
IF @b LIKE @pat SELECT 1
ELSE SELECT 0
但是我不能把它放在WHERE条件下
解决方法
您首先需要将要检查的变量拆分成行,并删除重复项.只有几个字符,你可以简单地使用一个表值构造函数：
DECLARE @b varchar(5) = 'DCA'; SELECT DISTINCT Letter = SUBSTRING(@b,n.Number,1) FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS n (Number) WHERE n.Number <= LEN(@b)
这使：
Letter ---------- D C A
现在您可以将其与列进行比较,并将其仅限于列包含所有字母的列(在HAVING子句中完成)
DECLARE @b varchar(5) = 'DCA';
WITH Letters AS
( SELECT DISTINCT Letter = SUBSTRING(@b,1)
FROM (VALUES(1),(10)) AS n (Number)
WHERE n.Number <= LEN(@b)
)
SELECT *
FROM (VALUES ('AA'),('ABCD'),('ABCDEFG'),('CAB'),('NA')) AS t (Col)
WHERE EXISTS
( SELECT 1
FROM Letters AS l
WHERE t.Col LIKE '%' + l.Letter + '%'
HAVING COUNT(DISTINCT l.Letter) = (SELECT COUNT(*) FROM Letters)
);
如果您的变量可能长于10个字符,则可能需要采用略有不同的字符串拆分方法.我仍然会使用数字来做到这一点,而是使用Itzik Ben-Gan’s stacked CTE method：
WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1)) AS n (N)),N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2) SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3;
这将给您一组从1到10,000的数字,您可以根据需要简单地添加更多的CTE和交叉连接来扩展该过程.所以用一个更长的字符串你可能有：
DECLARE @b varchar(5) = 'DCAFGHIJKLMNEOPNFEDACCRADFAE';
WITH N1 AS (SELECT N FROM (VALUES(1),N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),Numbers (Number) AS (SELECT TOP (LEN(@b)) ROW_NUMBER() OVER(ORDER BY N) FROM N3),Letters AS (SELECT DISTINCT Letter = SUBSTRING(@b,1) FROM Numbers AS n)
SELECT *
FROM (VALUES ('ABCDDCAFGHIJKLMNEOPNFEDACCRADFAEEFG'),('NA')) AS t (Col)
WHERE EXISTS
( SELECT 1
FROM Letters AS l
WHERE t.Col LIKE '%' + l.Letter + '%'
HAVING COUNT(DISTINCT l.Letter) = (SELECT COUNT(*) FROM Letters)
);
