0

I have a column with a letter followed by either numbers or letters:

ID_Col
------
S001
S1001
S090
SV911
SV800
Sfoofo
Szap
Sbart

How can I order it naturally with the numbers first (ASC) then the letters alphabetically? If it starts with S and the remaining characters are numbers, sort by the numbers. Else, sort by the letter. So SV911should be sorted at the end with the letters since it also contains a V. E.g.

ID_Col
------
S001
S090
S1001
Sbart
Sfoofo
SV800
SV911
Szap

I see this solution uses regex combined with the TO_NUMBER function, but since I also have entries with no numbers this doesn't seem to work for me. I tried the expression:

ORDER BY 
    TO_NUMBER(REGEXP_SUBSTR(ID_Col, '^S\d+$')), 
    ID_Col 

    /* gives ORA-01722: invalid number */
sushi
  • 274
  • 1
  • 4
  • 13
  • The regular expression in your query isn't the one in the answer you referred to. I recommend rereading the answer, and correcting the regular expression. Hint: test it in the SELECT block, and without the TO_NUMBER(), so see if it is returning what you expect it to. – MatBailie Mar 03 '20 at 21:50
  • @MatBailie I believe "\d+" is equivalent to the "[0-9]+" in the regular expression right? Anyways, yes it does return what I expected. I also tried the "[0-9]+" as well as "[[:digit:]]+" which gives the same error. – sushi Mar 03 '20 at 22:10

1 Answers1

3

Would this help?

SQL> with test (col) as
  2  (select 'S001'   from dual union all
  3   select 'S1001'  from dual union all
  4   select 'S090'   from dual union all
  5   select 'SV911'  from dual union all
  6   select 'SV800'  from dual union all
  7   select 'Sfoofo' from dual union all
  8   select 'Szap'   from dual union all
  9   select 'Sbart'  from dual
 10  )
 11  select col
 12  from test
 13  order by substr(col, 1, 1),
 14    case when regexp_like(col, '^[[:alpha:]]\d') then to_number(regexp_substr(col, '\d+$')) end,
 15    substr(col, 2);

COL
------
S001
S090
S1001
Sbart
Sfoofo
SV800
SV911
Szap

8 rows selected.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • how can I account for entries with multiple letters and numbers? I forgot to add it in the question, just added the example, E.g. SV911. With your solution, I get [S911, SV911, S912], but I want the SV911 sorted with the alphabets at the end. – sushi Mar 03 '20 at 22:24