Extract numbers from an alphanumeric string

In this example we have an alphanumeric string, and we want to extract only the numbers from it.

select regexp_substr(col1, '[0-9]*')
from table;

You could also use :digit: to represent numbers, but I prefer 0-9.

You might wonder why you would extract only numbers from a string. One example might be if you want to order by a house number. The presence of 1a,1b etc means that Oracle would order it as if it were a string, which is not ideal. Combining to_number with the regexp_substr will allow you to order the results in a much more sensible way.

Leave a Reply