 | |
Find the next word using regular expressions in Oracle 10g
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Find the next word using regular expressions in Oracle 10g -
02-09-2009
, 10:55 AM
#2
| |||
| |||
|
|
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 |
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
#3
| |||
| |||
|
|
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 |
2 data as (
3 select '(ADDRESS =
4 (CITY_DETAILS = (STREET# = 416)
5 (STREET_NAME =
6 UNITY BLVD.)
7 (CITY = AUSTIN))
8 (STATE_DETAILS = (STATE_NAME =
9 TEXAS)(ZIPCODE = 12345)))' val
10 from dual
11 )
12 select regexp_replace(replace(val,'
13 ',''), '^.*STATE_NAME[[:space:]]*=[[:space:]]*([[:alpha:]]+)[[:space:]]*\).*$','\1')
14 from data
15 /
REGEX
-----
TEXAS
1 row selected.
Regards
Michel
#4
| |||
| |||
|
|
admin (AT) rbtron (DOT) 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 |
Regards
Michel
#5
| |||
| |||
|
#6
| |||
| |||
|
|
Awesome answers! ... Thanks a lot ... just one more thing: Sometimes the file will have : (STATE_DETAILS = (STATE_NAME = * * * * * * * * * * * * * * * * * * * TEXAS)(ZIPCODE = 12345)) or (STATE_DETAILS = (TERRITORY = * * * * * * * * * * * * * * * * * * * TEXAS)(ZIPCODE = 12345)) So how to find the word after : either STATE_NAME or TERRITORY ? Thanx once again! -GC |
TERRITORY] and it worked fine ...
Two other things:
1) Some files have Territory as US.TEXAS ... in such cases, the script
outputs only US not US.TEXAS
2) Some files don't have STATE_NAME or TERRITORY in them. I need to
output the word NULL in such cases.
Thanks!
#7
| |||
| |||
|
|
On Feb 9, 3:35 pm, ad... (AT) rbtron (DOT) com wrote: Awesome answers! ... Thanks a lot ... just one more thing: Sometimes the file will have : (STATE_DETAILS = (STATE_NAME = TEXAS)(ZIPCODE = 12345)) or (STATE_DETAILS = (TERRITORY = TEXAS)(ZIPCODE = 12345)) So how to find the word after : either STATE_NAME or TERRITORY ? Thanx once again! -GC Ok, I got that boolean part ... I modified it to [STATE_NAME | TERRITORY] and it worked fine ... Two other things: 1) Some files have Territory as US.TEXAS ... in such cases, the script outputs only US not US.TEXAS 2) Some files don't have STATE_NAME or TERRITORY in them. I need to output the word NULL in such cases. Thanks! |
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 union all
10 select '(ADDRESS =
11 (CITY_DETAILS = (STREET# = 416)
12 (STREET_NAME =
13 UNITY BLVD.)
14 (CITY = AUSTIN))
15 (STATE_DETAILS = (TERRITORY =
16 US.TEXAS)(ZIPCODE = 12345)))' s
17 from dual union all
18 select '(ADDRESS =
19 (CITY_DETAILS = (STREET# = 416)
20 (STREET_NAME =
21 UNITY BLVD.)
22 (CITY = AUSTIN))
23 (STATE_DETAILS = (STATE_NAM =
24 TEXAS)(ZIPCODE = 12345)))' s
25 from dual
26 )
27 select
28 nullif(
29 regexp_replace(s,
30 '.*(STATE_NAME|TERRITORY)\s*=\s*((\w|\.)+).*',
31 '\2',
32 1,
33 1,
34 'mn'),s
35 ) rr
36 from t
37 ;
RR
-----------------------------------------------------------------------------------------------------------------------------
TEXAS
US.TEXAS
NULL
If your client is not sqlplus, wrap whole expression into nvl
Best regards
Maxim
#8
| |||
| |||
|
|
ad... (AT) rbtron (DOT) com schrieb: On Feb 9, 3:35 pm, ad... (AT) rbtron (DOT) com wrote: Awesome answers! ... Thanks a lot ... just one more thing: Sometimes the file will have : (STATE_DETAILS = (STATE_NAME = * * * * * * * * * * * * * * * * * * * TEXAS)(ZIPCODE = 12345)) or (STATE_DETAILS = (TERRITORY = * * * * * * * * * * * * * * * * * * * TEXAS)(ZIPCODE = 12345)) So how to find the word after : either STATE_NAME or TERRITORY ? Thanx once again! -GC Ok, I got that boolean part ... I modified it to [STATE_NAME | TERRITORY] and it worked fine ... Two other things: 1) Some files have Territory as US.TEXAS ... in such cases, the script outputs only US not US.TEXAS 2) Some files don't have STATE_NAME or TERRITORY in them. I need to output the word NULL in such cases. Thanks! SQL> set NULL NULL 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 union all * 10 *select '(ADDRESS = * 11 * * * * *(CITY_DETAILS = * *(STREET# = 416) * 12 * * * * * * * * * * * *(STREET_NAME = * 13 * * * * * * * * * * * *UNITY BLVD.) * 14 * * * * * * * * * * * *(CITY = AUSTIN)) * 15 * * * * *(STATE_DETAILS = (TERRITORY = * 16 * * * * * * * * * * * * * * * * * * * *US.TEXAS)(ZIPCODE = 12345)))' s * 17 *from dual union all * 18 *select '(ADDRESS = * 19 * * * * *(CITY_DETAILS = * *(STREET# = 416) * 20 * * * * * * * * * * * *(STREET_NAME = * 21 * * * * * * * * * * * *UNITY BLVD.) * 22 * * * * * * * * * * * *(CITY = AUSTIN)) * 23 * * * * *(STATE_DETAILS = (STATE_NAM = * 24 * * * * * * * * * * * * * * * * * * * *TEXAS)(ZIPCODE = 12345)))' s * 25 *from dual * 26 *) * 27 *select * 28 *nullif( * 29 * * *regexp_replace(s, * 30 * * * * *'.*(STATE_NAME|TERRITORY)\s*=\s*((\w|\.)+).*', * 31 * * * * *'\2', * 32 * * * * *1, * 33 * * * * *1, * 34 * * * * *'mn'),s * 35 * * *) rr * 36 *from t * 37 *; RR ---------------------------------------------------------------------------*-------------------------------------------------- TEXAS US.TEXAS NULL If your client is not sqlplus, wrap whole expression into nvl Best regards Maxim- Hide quoted text - - Show quoted text - |
4 words : You are the best !!!
#9
| |||
| |||
|
|
ad... (AT) rbtron (DOT) 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
#10
| |||
| |||
|
|
On 9 Feb, 18:17, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote: ad... (AT) rbtron (DOT) 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 |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.