Using Instr and substring [message #294744] |
Fri, 18 January 2008 15:25  |
pweill
Messages: 11 Registered: December 2007
|
Junior Member |
|
|
I need to pull off the 2nd parenthesis in a field. Examples:
EMI Eng OFC(866) Renov (E247)
I have the following code that works if there is one paranthesis in the field but not if there are two. So for the 1st example above I get (866) but I really want (E247). Here's my code:
(substr(GLV.pa_project_name,instr(GLV.pa_project_name,'(') + 1, (instr(GLV.pa_project_name,')') - instr(GLV.pa_project_name,'(') -1)))
How can I modify it to get the 2nd occurence (E247)...the info I want is always at the end of the record and is in parenthesis.
Also, if there is not a space between the first parenthesis how might it work?
Ex: POWER SIL GROWTH(E213)
THANKS!!
|
|
|
Re: Using Instr and substring [message #294749 is a reply to message #294744] |
Fri, 18 January 2008 22:16   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
10g+
SQL> select name, regexp_replace(name, '.*\(([^)]*)\)$','\1') sub
2 from t;
NAME SUB
------------------------------ ----------
EMI Eng OFC(866) Renov (E247) E247
POWER SIL GROWTH(E213) E213
NONE NONE
All versions:
SQL> select name, translate(substr(name, instr(name, '(', -1)),'1()','1') sub
2 from t;
NAME SUB
------------------------------ ----------
EMI Eng OFC(866) Renov (E247) E247
POWER SIL GROWTH(E213) E213
NONE NONE
|
|
|
Re: Using Instr and substring [message #294752 is a reply to message #294744] |
Fri, 18 January 2008 22:44   |
mshrkshl
Messages: 247 Registered: September 2006 Location: New Delhi
|
Senior Member |
|
|
SQL> ed
Wrote file afiedt.buf
1 select (substr(GLV.pa_project_name,
2 instr(GLV.pa_project_name,'(',1,2)
3 ))
4* from glv
SQL> /
(SUBSTR(GLV.PA_PROJECT_NAME,INSTR(GLV.PA_PROJECT_N
--------------------------------------------------
(E247)
|
|
|
Re: Using Instr and substring [message #294755 is a reply to message #294744] |
Fri, 18 January 2008 23:06   |
mshrkshl
Messages: 247 Registered: September 2006 Location: New Delhi
|
Senior Member |
|
|
if you want to take only string inside ( ).
SQL> ed
Wrote file afiedt.buf
1 select (substr(GLV.pa_project_name,
2 instr(GLV.pa_project_name,'(',1,2)+1,
3 instr(GLV.pa_project_name,')',1,2)-instr(GLV.pa_project_name,'(',1,2)-1))
4* from glv
SQL> /
(SUBSTR(GLV.PA_PROJECT_NAME,INSTR(GLV.PA_PROJECT_N
--------------------------------------------------
E247
|
|
|
|