Extracting letter and number sequences from a string
with strings as (
select 'ABC123' str from dual union all
select 'A1B2C3' str from dual union all
select '123ABC' str from dual union all
select '1A2B3C' str from dual
)
select regexp_substr(str, '[0-9]') First_Occurrence_of_Number,
regexp_substr(str, '[0-9].*') Num_Followed_by_String,
regexp_substr(str, '[A-Z][0-9]') Letter_Followed_by_String
from strings
FIRST_OCCURRENCE_OF_NUMBER | NUM_FOLLOWED_BY_STRING | LETTER_FOLLOWED_BY_STRING | 1 | 123 | C1 | 1 | 1B2C3 | A1 | 1 | 123ABC | - | 1 | 1A2B3C | A2 |
---|