查找varchar变量中最后一个数值的序列

I have a column in a table which has incremented values like:

我在表中有一列增加了如下值:

AAA0000001
AAA0000002 

... and so on

…等等

I want to find if the values stored in this column are in proper sequential order or if any value is missing in between or is deleted.

我想要查找这个列中存储的值是否按正确的顺序排列，或者是否有任何值丢失或被删除。

How can i achieve this?

我怎样才能做到这一点呢?

4 个解决方案

#1

Assuming the pattern is always: AAA[0-9][0-9][0-9][0-9][0-9][0-9][0-9], you can do this with a Tally Table.

假设总是:AAA[0-9][0-9][0-9][0-9][0-9][0-9][0-9] [0-9]

Sample Data:

样本数据:

CREATE TABLE Tbl(val VARCHAR(10))
INSERT INTO Tbl VALUES
('AAA0000001'), ('AAA0000002'), ('AAA0000004'), ('AAA0000011');

val
----------
AAA0000001
AAA0000002
AAA0000004
AAA0000011

SQL Fiddle

SQL小提琴

;WITH Cte AS(
    SELECT *,
        num = CAST(SUBSTRING(val, 4, LEN(val) - 3) AS INT)
    FROM Tbl
),
E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(SELECT MAX(num) FROM Cte)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
)
SELECT
    N, 
    val = 'AAA' + RIGHT('0000000' + CAST(N AS VARCHAR(7)), 7)
FROM Tally
WHERE NOT EXISTS(
    SELECT 1 FROM Cte WHERE num = N
)

RESULT

结果

N                    val
-------------------- ----------
3                    AAA0000003
5                    AAA0000005
6                    AAA0000006
7                    AAA0000007
8                    AAA0000008
9                    AAA0000009
10                   AAA0000010

---

Explanation:

解释:

1. The first CTE, named as Cte, extracts the numeric part of the strings and CASTs them to INT.
2. 第一个CTE命名为CTE，它提取字符串的数字部分并将它们转换为INT。
3. The succeeding CTEs, from E1 to Tally(N) generates a table with sequential values from 1 up to the MAX(num) - the INT return from the first CTE.
4. 接下来的CTE，从E1到计数(N)生成一个具有从1到MAX(num)的顺序值的表——从第一个CTE返回的INT值。
5. The final SELECT just checks for the non-existing num from the first CTE.
6. 最后的选择只是从第一个CTE检查不存在的num。
7. 'AAA' + RIGHT('0000000' + CAST(N AS VARCHAR(7)), 7) transforms N so that it follows the pattern.
8. “AAA”+右(“0000000”+ CAST(N为VARCHAR(7))))， 7)转换N，使其遵循模式。

#2

This is a Gaps problem. You can look into this article by Dwain Camps for more solutions on Gaps and Islands.

这是一个差距问题。您可以查看Dwain camp的这篇文章，了解关于差距和岛屿的更多解决方案。

You can use ROW_NUMBER like this.

可以像这样使用ROW_NUMBER。

Sample Data

样本数据

DECLARE @tab1 TABLE(id VARCHAR(20));

insert into @tab1 VALUES('AAA0000001'),('AAA0000002'),('AAA0000003'),('AAA0000004'),('AAA0000006'),('AAA0000007'),('AAA0000010');

Query

查询

;WITH CTE as 
(
SELECT convert(int,STUFF(id,1,3,'')) id,convert(int,STUFF(id,1,3,'')) - ROW_NUMBER()OVER(ORDER BY convert(int,STUFF(id,1,3,''))) rn
FROM @tab1
),CTE2 as 
(
SELECT ROW_NUMBER()OVER(ORDER BY rn) as rn, MIN(id) series_start,MAX(id) series_end
FROM CTE
GROUP BY rn
)
SELECT C2.series_end,C1.series_start
FROM CTE2 C1
INNER JOIN CTE2 C2 ON C1.rn = C2.rn + 1;

SQL Fiddle

SQL小提琴

Explanation

解释

• Output of CTE is the difference of gaps between id values.
• CTE的输出是id值之间的差值。
• Output of CTE2 is the start and end of continuous series of numbers
• CTE2的输出是连续数列的开始和结束
• Final Output gives the start and end of gaps within the series
• 最终输出给出了该系列中间隔的开始和结束

Output

输出

series_end  series_start
4   6
7   10

#3

If the schema is fixed then no need for complex queries. This works:

如果模式是固定的，那么就不需要复杂的查询。如此:

DECLARE @t TABLE ( v VARCHAR(100) );

INSERT  INTO @t
VALUES  ( 'AAA0000001' ),
        ( 'AAA0000002' ),
        ( 'AAA0000007' ),
        ( 'AAA0000008' ),
        ( 'AAA0000010' ),
        ( 'AAA0000011' ),
        ( 'AAA0000012' );


SELECT * FROM @t t1
CROSS APPLY(SELECT TOP 1 v FROM @t t2 WHERE t2.v > t1.v ORDER BY v) ca
WHERE RIGHT(t1.v, 7) <> RIGHT(ca.v, 7) - 1  

Output:

输出:

v           v
AAA0000002  AAA0000007
AAA0000008  AAA0000010

#4

In sqlserver 2012, you can use LAG and LEAD

在sqlserver 2012中，您可以使用LAG和LEAD

DECLARE @t table(col1 varchar(15))
INSERT @t values('AAA0000001'),('AAA0000002'),('AAA0000004')

SELECT 
  case when 
    stuff(lag(col1) over (order by col1), 1,3,'') + 1 
      = stuff(col1, 1,3,'') then 'Yes' else 'No' end previous_exists,
  case when
    stuff(lead(col1) over (order by col1), 1,3,'') - 1 
      = stuff(col1, 1,3,'') then 'Yes' else 'No' end next_exists,
  col1
FROM @t

Result:

结果:

previous_exists  next_exists  col1
No               Yes          AAA0000001
Yes              No           AAA0000002
No               No           AAA0000004