javaBeginner javaBeginner - 4 months ago 7
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.

using

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

doenst work either

Anybody have any ideas??

Edit:

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'

Answer

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

Output

ABC123
CDE456
GHI789

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 :

ABC123