CODEWITHSUNDEEP
sql-server
Pwavel002
Pwavel002

Reputation: 299

Finding sequence of the last numeric value in a varchar variable

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?

Upvotes: 2

Views: 116

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44326

In sqlserver 2012, you can use LAG and 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

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

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.

Sample Data:

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

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

SQL Fiddle

;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. 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.
  3. The final SELECT just checks for the non-existing num from the first CTE.
  4. 'AAA' + RIGHT('0000000' + CAST(N AS VARCHAR(7)), 7) transforms N so that it follows the pattern.

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 1

ughai
ughai

Reputation: 9890

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

You can use ROW_NUMBER like this.

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

Explanation

  • Output of CTE is the difference of gaps between id values.
  • Output of CTE2 is the start and end of continuous series of numbers
  • Final Output gives the start and end of gaps within the series

Output

series_end  series_start
4   6
7   10

Upvotes: 1

Related Questions