sql – 检查varchar中的字符

我如何检查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)
        );

sql – 检查varchar中的字符

解决方法

猜你在找的MsSQL相关文章