Re: Find the next word using regular expressions in Oracle 10g

From: Maxim <mdemenko_at_gmail.com>
Date: Tue, 10 Feb 2009 09:50:07 -0800 (PST)
Message-ID: <ebc7119e-8956-4776-af97-a6fa8bc99650_at_u13g2000yqg.googlegroups.com>

On 10 Feb., 13:17, gazzag <gar..._at_jamms.org> wrote:
> On 9 Feb, 18:17, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
> > ad..._at_rbtron.com schrieb:
>
> > > Hi,
>
> > > I have the following details in a file:
>
> > > (ADDRESS =
> > >         (CITY_DETAILS =    (STREET# = 416)
> > >                       (STREET_NAME =
> > >                       UNITY BLVD.)
> > >                       (CITY = AUSTIN))
> > >         (STATE_DETAILS = (STATE_NAME =
> > >                                       TEXAS)(ZIPCODE = 12345)))
>
> > > I need to pull word after the word STATE_NAME. In this case it is
> > > TEXAS. The required word will be enclosed within '=' and ')'. These
> > > are multiple line rows.
>
> > > How to do this using regular expressions in Oracle 10g please ?
>
> > > Thanx
>
> > > -GC
>
> > Somewhat like this ?
>
> > SQL> with t as (
> >    2  select '(ADDRESS =
> >    3          (CITY_DETAILS =    (STREET# = 416)
> >    4                        (STREET_NAME =
> >    5                        UNITY BLVD.)
> >    6                        (CITY = AUSTIN))
> >    7          (STATE_DETAILS = (STATE_NAME =
> >    8                                        TEXAS)(ZIPCODE = 12345)))' s
> >    9  from dual)
> >   10  select
> >   11  regexp_replace(s,'.*STATE_NAME\s*=\s*(\w+).*','\1',1,1,'mn') rr
> >   12  from t
> >   13  ;
>
> > RR
> > -----
> > TEXAS
>
> > Best regards
>
> > Maxim
>
> Maxim,
>
> Would you mind explaining how the REGEXP_REPLACE function is working
> in your example, please?  I am familiar with regular expressions but
> only on a fairly basic level.
>
> Cheers,
>
> -g

Sure,
the regexp_replace(s,'.*STATE_NAME\s*=\s*(\w+).*','\1',1,1,'mn') means:
match the original string as

1) .*   # anything followed by
2) STAT_NAME  # literally STAT_NAME followed by
3) \s* # zero or many whitespaces followed by
4) = # literally "=" sign followed by
5) \s* # zero or many whitespaces followed by
6) \w+ # one or many word (alphanumeric or _ ) characters followed by
7) .* anything

Part 6) is the part which was interesting for OP - any word which occurs right after "STATE_NAME = "
To be able backreference this part in the replace expression, it should be enclosed into brackets - so you define (up to 10) groups within your regexp, which can be then referenced later with \1, \2, \3 and so on.
In nonformal terms it sounds: match the whole string, define group within it which consists of a word following the "STAT_NAME = " and replace the match (again , whole string) by first backreferenced group (\1).
The rest of parameters means - search from the 1 position in a string and consider only the 1st match, besides that, consider the string as multiline string ('m' parameter) and consider the "." to match the newline symbols ( 'n' parameter) - and this is true in this example - the leading and trailing ".*" should cover newlines as well. Without the last two parameters, search would not be continued after the newline.

Best regards

Maxim Received on Tue Feb 10 2009 - 11:50:07 CST