Re: Find the next word using regular expressions in Oracle 10g
From: gazzag <gareth_at_jamms.org>
Date: Tue, 10 Feb 2009 10:13:30 -0800 (PST)
Message-ID: <1113be1e-17ba-4c85-bf4c-a8276c2fc1e8_at_j39g2000yqn.googlegroups.com>
On 10 Feb, 17:50, Maxim <mdeme..._at_gmail.com> wrote:
> 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
Date: Tue, 10 Feb 2009 10:13:30 -0800 (PST)
Message-ID: <1113be1e-17ba-4c85-bf4c-a8276c2fc1e8_at_j39g2000yqn.googlegroups.com>
On 10 Feb, 17:50, Maxim <mdeme..._at_gmail.com> wrote:
> 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
Maxim,
Many thanks for the excellent explanation.
Kind regards,
-g Received on Tue Feb 10 2009 - 12:13:30 CST