javaBeginner javaBeginner - 6 months ago 14
SQL Question

Oracle regexp_substr - Find and extract all occurances in a column

Im working with an Oracle DB and i´m trying to find and extract ALL occurances in a String matching a specific Pattern...

Its supposed to be 3Letter, 3 Numbers and then maybe a letter or not

i tried this:

SELECT REGEXP_SUBSTR(my_column, '[A-Za-z]{3}(\d)(\d)(\d)') AS values FROM my_table

but it only gives me the first occurance.


REGEXP_SUBSTR(my_column, '[A-Za-z]{3}(\d)(\d)(\d)', 0, 0, 'i')

doenst work either

Anybody have any ideas??


Im am Tryint to extract it from PLSQL files. So its pretty much like SQL querries like

select * from abc123 where some_value = 'some_value'


Try this query to break ABC123CDE456FGHI789 squence

with mine as (select 'ABC123CDE456FGH789' hello from dual) 
select regexp_substr(hello, '[A-Za-z]{3}(\d){3}', 1, level) STR from mine
connect by regexp_substr(hello, '[A-Za-z]{3}(\d){3}', 1, level) is not null



For get specific postion then you want to use

select regexp_substr('ABC123CDE456FGH789', '[A-Za-z]{3}(\d){3}', 1, i) STR from dual

change i value as per position like

select regexp_substr('ABC123CDE456FGH789', '[A-Za-z]{3}(\d){3}', 1, 1) STR from dual

Output :