order by a string with number and letter

Question

I want to order by a string in sql that have number in it and I want orderby number as number instead of string these are sample string

I try this but it does not work:

SELECT * FROM testTbl
   ORDER BY REPLACE(code,' ','')

Answer 1

You can try this, but I'm not entirely sure that I understand your question or your example.

 SELECT *
FROM testTbl
ORDER BY CAST(REGEXP_SUBSTR(code, '[0-9]+') AS NUMERIC_ORDER);