有没有一种方法可以在SQL中查找具有正向序列值的列

Question

我正在筛选具有正向序列值的记录。例如：我的查询是select col from mytable where col like '123456789' or col like '987654321'。有什么合乎逻辑的方法可以做到这一点吗？

Answer 1

如果您想要字符串中任意长度的数字序列(最多10个字符(，可以使用like:

where '0123456789' like concat('%', col, '%') or
'9876543210' like concat('%', col, '%') 

为了完整性，我添加了0。

Answer 2

根据ASCII/Unicode表，您似乎希望查找每个字符与其前一个字符按顺序排列的字符串

您可以使用计数函数或CTE将字符串拆分为单个字符，然后检查是否没有任何字符无序。

我使用了Itzik Ben Gan著名的交叉连接计数。根据字符串的大小添加或删除交叉联接。

WITH
L0 AS ( SELECT 1 AS c 
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ), --up to 16
L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),   -- up to 256
L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B )    -- up to 65536
SELECT *
FROM YourTable t
CROSS APPLY (VALUES(
CASE WHEN ASCII(LEFT(t.YourString, 1)) <
ASCII(RIGHT(t.YourString, 1))
THEN 1 ELSE -1 END
) ) v(IsAscending)
WHERE NOT EXISTS (
SELECT 1
FROM (
SELECT TOP (LEN(t.YourString) - 1)
rn = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM L2
) Nums
WHERE rn > 1 AND
ASCII(SUBSTRING(t.YourString, rn + 1, 1)) -
ASCII(SUBSTRING(t.YourString, rn, 1))
<> v.IsAscending
)

如果使用nvarchar，请将ASCII更改为UNICODE。