Oracle sql Order By number and char inside

Question

I'm quite new to sql so I don't fully understand what I'm doing. My question is, how can I order by this data, that the order would go by number like 1,2,3, but there are letters inside, so it would be like 1,2,A3, B3, 4.

I have this so far:

ORDER BY REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 1, 1)),'(\d+.)(\d+.)?(\d+.)?(\d+.)?', '\1') NULLS FIRST, REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 2, 2)),'(\d+.)(\d+.)?(\d+.)?(\d+.)?', '\2') NULLS FIRST, REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 3, 3)),'(\d+.)(\d+.)?(\d+.)?(\d+.)?', '\3') NULLS FIRST, REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 4, 4)),'(\d+.)(\d+.)?(\d+.)?(\d+.)?', '\4') NULLS FIRST, REGEXP_REPLACE(TRIM(REGEXP_SUBSTR(string, '\d', 5, 5)),'(\d+.)(\d+.)?(\d+.)?(\d+.)?', '\5') NULLS FIRST, string

it works until we have a third level decimal, like 2.7.1., then it goes like 2.1, 2.2...2.7,2.8, 2.7.1., 2.7.2. I don't know if I'm explaining it correctly so I have pic too :D

When it's like this it works: Picture of Working sort

Picture of Working sort

But then we have numbers like 2.7.1., 2.7.2. Picture with incorrect sort

So the plan is that it would sort number with string too, like: 1,2,A3,A3.1, A3.1.1. B3,B3.1,4.1, 4.1.1, 4.1.2...

Thank you so much for your help and explaining.

Answer 1

The immediate problem is that you are incrementing the positionas well as the occurrence; so

REGEXP_SUBSTR(string, '\d', 2, 2)

should be

REGEXP_SUBSTR(string, '\d', 1, 2)

But you're overcomplicating it, and not handling multiple-digit elements, either when extracting or when sorting as nothing is treated as a number.

I think this does what you want:

ORDER BY
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 1)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 2)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 3)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 4)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 5)) nulls first,
  string

db<>fiddle

---

If only the first element can start with a character (or characters), then you just just add a check for that after the first digit:

ORDER BY
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 1)) nulls first,
  REGEXP_SUBSTR(string, '^\w+', 1, 1) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 2)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 3)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 4)) nulls first,
  TO_NUMBER(REGEXP_SUBSTR(string, '\d+', 1, 5)) nulls first

which will order 3,A3,A3.1,B3.

You might not need the string at the end now.

db<>fiddle