Skip to content
Advertisement

Oracle SQL select characters after numeric portion of a string

I have the string “Serenity Lane – Barbur Boulevard 10920 SW Barbur Blvd Portland, OR 97219” and I want to select the first 13 characters from it :10920 SW Barb

Is there a way to select only the first 13 characters after the first numeric character? In this example, the first thirteen characters starting at 1.

Advertisement

Answer

You can use regexp_substr():

regexp_substr(mystring, 'd.{12}')

The regex searches for the first digit in the string, and captures it, along with the 12 following characters.

Demo on DB Fiddle:

with t as (select 'Serenity Lane - Barbur Boulevard 10920 SW Barbur Blvd Portland, OR 97219' mystring from dual)
select mystring, regexp_substr(mystring, 'd.{12}') string_part
from t
MYSTRING                                                                 | STRING_PART  
:----------------------------------------------------------------------- | :------------
Serenity Lane - Barbur Boulevard 10920 SW Barbur Blvd Portland, OR 97219 | 10920 SW Barb
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement