Extract number (with different delimiters) from string using REGEXP_SUBSTR in plsql

Question

I need to extract patterned numbers from I/P string. I have the following patterns:
xxx-xxx-xxxx
xxx xxx-xxxx
xxx xxx xxxx

I am using this query to find matching string:

select REGEXP_substr('phn: 678 987-0987 Date: 12/2029',
                 '[0-9]{3}(\-|\  |\ )[0-9]{3}(\-|\--|\ )[0-9]{4}')
from dual;

I also want to extract the following patterns:
xxxxxx-xxxx
xxxxxxxxxx
etc...

Where do I modify the query?

You wrote the code, why wouldn't you know better? Please see [Reference - What does this regex mean?](http://stackoverflow.com/q/22937618/3622940) — Unihedron, Sep 15 '14 at 15:36

Avinash Raj · Accepted Answer · 2014-09-15T15:46:14.653

2

Change your regex to,

[0-9]{3}(\-|\  |\ )?[0-9]{3}(\-|\--|\ )?-?[0-9]{4}

DEMO

(\-|\ |\ )? turns the whole group as optional. And -? turns - as optional. The function of ? after a character literal is, it makes the preceding token as optional.

edited Sep 15 '14 at 15:46

answered Sep 15 '14 at 15:39

Avinash Raj

160,498
22
182
229

Thanks!! By the way, what does `?-?` mean here? – termite_paste Sep 15 '14 at 15:42
Thanks! I got confused... I modified the regex to `[0-9]{3}\ ?-?(\ )?[0-9]{3}\ ?-?(\ )?[0-9]{4}` – termite_paste Sep 15 '14 at 15:52

score 0 · Answer 2 · edited Sep 15 '14 at 16:17

Regular expressions are not always a good approach, since they are high resource consuming feature. I would still use old SUBSTR + INSTR technique :

16777216 * to_number(substr(ip, 1, instr(ip, '.', 1, 1) - 1))
     + 65536 * to_number(substr(ip, instr(ip, '.', 1, 1) + 1, instr(ip, '.', 1, 2) - instr(ip, '.', 1, 1) - 1))
     + 256 * to_number(substr(ip, instr(ip, '.', 1, 2) + 1, instr(ip, '.', 1, 3) - instr(ip, '.', 1, 2) - 1))
     + to_number(substr(ip, instr(ip, '.', 1, 3) + 1))

IP# is a simple 32-bit (4 bytes) integer; which is being presented in "dotted quad" format. Each byte will contain a value between 0 and 255. so converting to number & using between is as efficient as possible.

Extract number (with different delimiters) from string using REGEXP_SUBSTR in plsql

2 Answers2