Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Regular Expressions; return a buried match

1 view
Skip to first unread message

cate

unread,
Feb 3, 2010, 5:43:59 PM2/3/10
to
select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table;

I want the 5th and 6th digit. Can Oracle do this?

vsevolod afanassiev

unread,
Feb 3, 2010, 7:07:34 PM2/3/10
to

To extract 5th and 6th digit:

substr(encodedField,5,2)

Malcolm Dew-Jones

unread,
Feb 3, 2010, 6:14:08 PM2/3/10
to
cate (catebe...@yahoo.com) wrote:
: select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table;

: I want the 5th and 6th digit. Can Oracle do this?

In 10GR1 not directly in one regexp. In this case substr(result,5,2)
would help. In other versions with regexp's I assume the same, but you
could always check yourself
google: REGEXP_SUBSTR 10GR2 download

Maxim Demenko

unread,
Feb 4, 2010, 2:28:10 AM2/4/10
to cate
On 03.02.2010 23:43, cate wrote:
> select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table;
>
> I want the 5th and 6th digit. Can Oracle do this?

Assumed, you always have 6 consequent digits at the beginning, you could
do something like this (in pre 10gR2 you have to use [0-9] instead of \d)

SQL> with t as (
2 select '123456def' s from dual union all
3 select '123490def879' from dual
4 )
5 select s,regexp_replace(s,'^(\d{4})(\d\d).*','\2') r
6 from t;

S R
-------------------- --------
123456def 56
123490def879 90


Best regards

Maxim

"Álvaro G. Vicario"

unread,
Feb 4, 2010, 7:26:41 AM2/4/10
to
El 03/02/2010 23:43, cate escribi�/wrote:
> select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table;
>
> I want the 5th and 6th digit. Can Oracle do this?

I once did similar stuff inside a function. I did something like this:

IP1 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 1));
IP2 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 2));
IP3 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 3));
IP4 := TO_NUMBER(REGEXP_SUBSTR(IP_CADENA, '[0-9]+', 1, 4));

This should work on Oracle 10 or greater.


--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

Walt

unread,
Feb 4, 2010, 3:27:16 PM2/4/10
to
cate wrote:
> select REGEXP_SUBSTR(encodedField, '^\d{4}(\d\d)') from table;
>
> I want the 5th and 6th digit. Can Oracle do this?

If the 5th and 6th digit have meaning on their own, then they should
have been modeled as their own field(s) instead of concatenated into a
composite field.

That said, yes, one can extract them with simple expressions. Several
examples have already been given. I don't see why regular expressions
are necessary, but they will work. SUBSTR should work, unless the data
is more hinky than you've let on.

//Walt

cate

unread,
Feb 4, 2010, 7:17:50 PM2/4/10
to


Thanks all. I think we'll surrender and let perl handle this. Walt,
I'll forward your comment to the client. :-)

"Álvaro G. Vicario"

unread,
Feb 5, 2010, 6:28:03 AM2/5/10
to

If it's fixed length data, I definitively agree with you.

0 new messages