SQL query to find rows

database, oracle, sql

Solution

You could generate the full list of number and then with a NOT EXISTS (or NOT IN) filter the existing records out. For example:

SELECT new_roll_no
  FROM (SELECT 'CD01 ' || to_char(rownum, 'fm0000') new_roll_no FROM dual 
        CONNECT BY LEVEL <= (SELECT to_number(MAX(substr(roll_no, 6))) FROM T))
 WHERE new_roll_no NOT IN (SELECT roll_no FROM T)
 ORDER BY 1

Here's how it works:

- CONNECT BY LEVEL is a bit of a trick query, it will generate rows up to the level you indicate (in this case I chose the max from your table). I think this query first appeared on the askTom site and is now quite widespread.

- I've generated 8 rows, I will next use the TO_CHAR function to make a column look like your primary key (see the format model documentation of the TO_CHAR function).

- I then compare these generated values to your actual table value (NOT IN is an ANTI-JOIN)

Problem

I have a table `mytable` in ORACLE database having only one column `Roll_no` like following: ``` Roll_no ---------- CD01 0001 CD01 0002 CD01 0004 CD01 0005 CD01 0008 ``` here `CD01` is fixed, after `CD01` one space is there and then numbers are written `0001` and onwards. Please see here that `CD01 0003`, `CD01 0006`, `CD01 0007` are missing and these are my required output. Required output: ``` Roll_no ---------- CD01 0003 CD01 0006 CD01 0007 ``` How can I write a SQL query to find out these missing alphanumeric characters? Any ideas please

Original source