Home » SQL & PL/SQL » SQL & PL/SQL » Using Instr and substring
Using Instr and substring [message #294744] Fri, 18 January 2008 15:25 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Using Instr and substring [message #295562 is a reply to message #294749] Tue, 22 January 2008 14:40 Go to previous message
pweill
Messages: 11
Registered: December 2007
Junior Member
Thank you!!
Previous Topic: which query will be having better performance
Next Topic: Privileges for Materialized views
Goto Forum:
  


Current Time: Mon Feb 17 22:21:23 CST 2025