Oracle SQL Developer:How do I select entries in a data based on the nature of entries in a particula-CodePudding

I have the following table:

Name	ID
John	12345678
Jim	23456789
Alice	34567890
Eric	234/15
Joe	13/786

The datatype of the ID column is varchar(255). As you can see, there are two formats for the ID: an eight digit numeric one and one where the ID is of the format xx/xxx or xxx/xx.

I want to separate out the entries which have ID in thee eight digit numeric format. How should I structure my WHERE clause?

CodePudding user response：

If you are looking to only pull the eight digit ID rows, then a simple not like will work:

select *
from tbl
where tbl.ID not like '%/%'

If you wanted to pull the XXX/XX format, you can do:

where tbl.ID like '___/__'

And for the XX/XXX format, just switch around the underscores:

where tbl.ID like '__/___'

CodePudding user response：

There are zillion options, you just have to pick one you find the most appropriate. Here are just some of them.

Sample data:

SQL> select * From test;

NAME  ID
----- --------
John  12345678
Jim   23456789
Alice 34567890
Eric  234/15
Joe   13/786

ID length:

SQL> select name, id,
  2    case when length(id) = 8 then 'digit'
  3         else 'xxx'
  4    end what
  5  from test;

NAME  ID       WHAT
----- -------- -----
John  12345678 digit
Jim   23456789 digit
Alice 34567890 digit
Eric  234/15   xxx
Joe   13/786   xxx

Does the ID contain slash?

SQL> select name, id,
  2    case when instr(id, '/') = 0 then 'digit'
  3         else 'xxx'
  4    end what
  5  from test;

NAME  ID       WHAT
----- -------- -----
John  12345678 digit
Jim   23456789 digit
Alice 34567890 digit
Eric  234/15   xxx
Joe   13/786   xxx

Regular expressions: is the ID all digits?

SQL> select name, id,
  2    case when regexp_like(id, '^\d $') then 'digit'
  3         else 'xxx'
  4    end what
  5  from test;

NAME  ID       WHAT
----- -------- -----
John  12345678 digit
Jim   23456789 digit
Alice 34567890 digit
Eric  234/15   xxx
Joe   13/786   xxx