CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
Artur Stolc
Artur Stolc

Reputation: 41

Find a missing values from a table

Is there a way to search in varchar column.

I got table called x with row named par and data in: 

150/RXRPR1/18/0020642
150/RXRPR1/18/0020640
150/RXRPR1/18/0020639
151/RXRPR1/18/0020638
151/RXRPR1/18/0020637
151/RXRPR1/18/0020636
151/RXRPR1/18/0020634

The row is missing 

150/RXRPR1/18/0020641
151/RXRPR1/18/0020635

How to write a SQL statement to search the table for search the missing data?

The data is of type varchar and I have permissions to select only in database

Upvotes: 1

Views: 535

Answers (2)

Anson Aricatt
Anson Aricatt

Reputation: 393

You can try this query:

Please replace @MINVAL,@MAXVAL with respective values.

declare @t table([VALUE] varchar(50))
insert into @t([VALUE]) values
('150/RXRPR1/18/0020642'),
('150/RXRPR1/18/0020640'),
('150/RXRPR1/18/0020639'),
('151/RXRPR1/18/0020638'),
('151/RXRPR1/18/0020637'),
('151/RXRPR1/18/0020636'),
('151/RXRPR1/18/0020634')

DECLARE @MINVAL INT = 20634, 
        @MAXVAL INT = 20642; 

WITH cte 
     AS (SELECT @MINVAL VAL 
         UNION ALL 
         SELECT val + 1 VAL 
         FROM   cte 
         WHERE  val < @MAXVAL) 
SELECT missing 
FROM   (SELECT *, 
               Replace (Lead([VALUE]) OVER ( ORDER BY val)
               , CONVERT (INT, RIGHT (Lead([VALUE]) OVER (ORDER BY val), 7)), 
               val) 
               MISSING 
        FROM   cte 
               LEFT JOIN @t X 
                      ON CONVERT (INT, RIGHT (X.[VALUE], 7)) = cte.val) X 
WHERE  [VALUE] IS NULL OPTION ( MAXRECURSION  10000)

Upvotes: 0

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

You can use classical gaps detection in the right 7 chars on your numbers, partitioned by the left 14 chars. Use LEAD function to find the value of the next record and check is the difference between the current value and next value greater than 1. This will detect the gaps and you can calculate the start and the end of the gap by adding 1 to the current value and subtracting one from the next value. Something like this:

declare @t table(col varchar(50))
insert into @t(col) values
('150/RXRPR1/18/0020642'),
('150/RXRPR1/18/0020640'),
('150/RXRPR1/18/0020639'),
('151/RXRPR1/18/0020638'),
('151/RXRPR1/18/0020637'),
('151/RXRPR1/18/0020636'),
('151/RXRPR1/18/0020634')

SELECT
     gapStart = left([current], 14) + right('000000' + cast(cast(right([current], 7) as int) + 1 as varchar(10)), 7)
    ,gapEnd   = left([next], 14) + right('000000' + cast(cast(right([next], 7) as int) - 1 as varchar(10)), 7)
FROM
(
SELECT
    [current] = col
   ,[next]    = LEAD(col) OVER (partition by left(col, 14) ORDER BY col)
FROM @t
) tmp
WHERE cast(right([next], 7) as int) - cast(Right([current], 7) as int) > 1;

Upvotes: 2

Related Questions