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?
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
