Oracle regexp_substr - Find and extract all occurances in a column

Question

I'm working with an Oracle DB and I'm trying to find and extract ALL occurrences in a string matching a specific pattern...

It's supposed to be 3 letters, 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 returns the first occurrence.

Using

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

doesn't work either

Does anybody have any ideas?

Edit:

I'm trying to extract it from PLSQL files. So its pretty much like SQL queries like

select * 
from abc123 
where some_value = 'some_value'

Please post some sample data and desired result; for example, what do you expect from 'ABC123CDE456FGHI789' ? — Aleksej, Aug 03 '16 at 08:57
@sanjay radadiya Yes it can accept 0 as a parameter. just not for Position. You can use it for the occurance parameter ... — javaBeginner, Aug 03 '16 at 09:14
give sample data as @aleksej suggest so its clear what you want . — Sanjay Radadiya, Aug 03 '16 at 09:18

Sanjay Radadiya · Accepted Answer · 2016-08-03T09:39:31.810

4

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

edited Aug 03 '16 at 09:39

answered Aug 03 '16 at 09:26

Sanjay Radadiya

1,254
15
22

Thanks alot. this really helps me. – javaBeginner Aug 03 '16 at 09:42
level query and regular expression is system facility so its always fast as compare to retrieve data from database. – Sanjay Radadiya Aug 03 '16 at 12:13

score 1 · Answer 2 · answered Aug 03 '16 at 09:37

Try to get number in 1.2.3 (suppose it is a domain of a country)

SELECT str,level,REGEXP_SUBSTR(str, '[0-9]', 1, LEVEL) AS substr
FROM (
SELECT country_domain str from country where regexp_like(country_domain, '[0-9]')
)
CONNECT BY LEVEL <= REGEXP_count(str, '[0-9]');

Output

STR    LEVEL SUBSTR
1.2.3   1     1
1.2.3   2     2
1.2.3   3     3

Oracle regexp_substr - Find and extract all occurances in a column

2 Answers2