CODEWITHSUNDEEP
sqloracle-database
dev219
dev219

Reputation: 3

SQL ORACLE order by numbers ans strings

I try to use:

select * from list order by  id;
select * from list order by  LPAD(id, 4);

But it not working.

I have this list in oracle table :

id (varchar2)
-----------
123
124
125
126
toto
bobo
koko
201
169

So I need a result like this:

123
124
125
126
169
201
bobo
koko
toto

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

I would suggest:

order by (case when regexp_like(id, '^[0-9]+$')
               then to_number(id)
          end) asc nulls last,
         id

This will handle numeric ids numerically, even if they are not all the same length.

Upvotes: 2

GMB
GMB

Reputation: 222422

You could add in the ORDER BY clause an additional expression that checks whether the value contains number only (in which case it should be given the priority), like:

SELECT *
FROM list
ORDER BY 
    CASE WHEN REGEXP_LIKE(id, '^[0-9]*$') THEN 1 ELSE 0 END,
    id

Upvotes: 0

Related Questions