REGEXP_SUBSTR to extract fixed length string starting from a digit

Question

Table A
ID     ID_Descr
1     'DUP 8002061286'
2     'DUP 8002082667  '
3     ' 8002082669 DUP'

I would like to extract the string from the ID_Descr field with the following conditions:

String always starts with 8
String length is always 10 digits

This means stripping everything to the right and left of the string (eg. '8002082669'). How can I achieve this? Using REGEXP_SUBSTR?

I am using Oracle 11g.

Thanks!

score 3 · Answer 1 · answered Mar 20 '15 at 02:23

3

Although you could use regexp_substr() for this, I would take a different approach. I would just look for the '8' using instr() and then take the next 10 characters:

select substr(id_descr, instr(id_descr, '8'), 10)

This seems like the simplest solution.

answered Mar 20 '15 at 02:23

Gordon Linoff

1,242,037
58
646
786

Thanks for your help Gordon! – Heisenberg Mar 20 '15 at 02:58

score 2 · Accepted Answer · answered Mar 20 '15 at 02:43

You could use REGEXP_SUBSTR() but a regex is an expensive operation so you would be much better off using SUBSTR() and INSTR():

SELECT SUBSTR(ID_Descr, INSTR(ID_Descr, '8'), 10) FROM tableA;

If you really wanted to use REGEXP_SUBSTR() you could do it as follows:

SELECT REGEXP_SUBSTR(ID_Descr, '8.{9}') FROM tableA;

This would get 8 plus the following 9 characters (. being the wildcard).

Now if you wanted to match only digits, then REGEXP_SUBSTR() would probably be your best bet:

SELECT REGEXP_SUBSTR(ID_Descr, '8[0-9]{9}') FROM tableA;

Thanks David, I appreciate you showing me the regexp query as well. It was perfect for what I needed. — Heisenberg, Mar 20 '15 at 02:58

REGEXP_SUBSTR to extract fixed length string starting from a digit

2 Answers2