| Need help on using regexp_substr in SQL [message #633241] |
Thu, 12 February 2015 17:47  |
 |
3183h
Messages: 4
Registered: December 2010
|
Junior Member |
|
|
I tried to extract the information in a field by using regexp_substr.
Samples of the data:
* Current Date updated ( 12/31/2015 -> 12/31/2016 )
* Status updated ( Open -> Should be Closed )
From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";
example 2 --> need outputs of "Open" and "Should be Closed"
These are what all records look like:
1. It contains the word, "updated".
2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)
3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)
4. The last part is to captured everything after the "->" but before the close parenthesis ")"
I tried using everything, including regexp_substr(log_action, '\S+(\s|-)+\S+\s+(.*)\s+\S+$', 4, 1, '', 2) but it still did not work.
Can anyone help? Let me know whether I use the correct function as well. Thank you.
|
|
|
|
|
|
| Re: Need help on using regexp_substr in SQL [message #633243 is a reply to message #633241] |
Thu, 12 February 2015 19:27  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could use SUBSTR and INSTR, as shown below.
SCOTT@orcl12c> SELECT * FROM your_table
2 /
LOG_ACTION
---------------------------------------------------
* Current Date updated ( 12/31/2015 -> 12/31/2016 )
* Status updated ( Open -> Should be Closed )
2 rows selected.
SCOTT@orcl12c> COLUMN output1 FORMAT A30
SCOTT@orcl12c> COLUMN output2 FORMAT A30
SCOTT@orcl12c> SELECT SUBSTR
2 (log_action,
3 INSTR (log_action, 'updated ( ') + 10,
4 INSTR (log_action, ' -> ') - INSTR (log_action, 'updated ( ') - 10)
5 AS output1,
6 SUBSTR
7 (log_action,
8 INSTR (log_action, ' -> ') + 4,
9 INSTR (log_action, ' )', -1) - INSTR (log_action, ' -> ') - 4)
10 AS output2
11 FROM your_table
12 /
OUTPUT1 OUTPUT2
------------------------------ ------------------------------
12/31/2015 12/31/2016
Open Should be Closed
2 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
