Home » SQL & PL/SQL » SQL & PL/SQL » Extract Data within the Parenthesis (Oralce SQL 9i)
Extract Data within the Parenthesis [message #395312] Wed, 01 April 2009 02:18 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
select  substr(substr('US(080-66533221)AA',instr(('US(080-66533221)AA'), '(')+1),1,
(instr((substr('US(080-66533221)AA',instr(('US(080-66533221)AA'), '(')+1)),')'))-1) "NUMBER" 
from dual;

NUMBER
------------
080-66533221

I am trying to extract the data which is there inside the parenthesis. I have written the above query but I feel that it is very complex. Is there any simple query to get the desired output? Please help
Re: Extract Data within the Parenthesis [message #395326 is a reply to message #395312] Wed, 01 April 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select 'US(080-66533221)AA' val from dual)
  2  select substr(val,
  3                instr(val,'(')+1,
  4                instr(val,')')-instr(val,'(')-1) val
  5  from data
  6  /
VAL
------------
080-66533221

1 row selected.

Regards
Michel
Re: Extract Data within the Parenthesis [message #395333 is a reply to message #395326] Wed, 01 April 2009 02:59 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
what is with data as? is that column names?
Re: Extract Data within the Parenthesis [message #395341 is a reply to message #395333] Wed, 01 April 2009 03:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
WITH just lets you give a sub-query a name, and refer to that name as a data source.
In this case Michel has chosen to call the subquery DATA.

If you're on 10g or higher, you can use Regular expressions:
with data as (select 'US(080-66533221)AA' val from dual)
  select regexp_substr(val,'[(].*[)]') val
        ,regexp_substr(val,'[^()]*',1,3) val
  from data
Re: Extract Data within the Parenthesis [message #395342 is a reply to message #395333] Wed, 01 April 2009 03:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
With is a way to name a (part of your) query.

The end-result would be the same as
select substr(val,
              instr(val,'(')+1,
              instr(val,')')-instr(val,'(')-1) val
from (select 'US(080-66533221)AA' val from dual) data


The benefit of using WITH is this case is to clearly separate the used data from the actual functionality of the query.
In other cases, you can use WITH to force/hint the optimizer to only execute a part of your query once.

[Edit: hm, beaten by JR]

[Updated on: Wed, 01 April 2009 03:27]

Report message to a moderator

Re: Extract Data within the Parenthesis [message #395343 is a reply to message #395341] Wed, 01 April 2009 03:27 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I am working on Oracle SQL 9i. So please provide me the query for the same. Thanks
Re: Extract Data within the Parenthesis [message #395348 is a reply to message #395343] Wed, 01 April 2009 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I did it.

Regards
Michel
Re: Extract Data within the Parenthesis [message #395355 is a reply to message #395342] Wed, 01 April 2009 03:40 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Why data is used in the from clause? Is that Table name?
Re: Extract Data within the Parenthesis [message #395361 is a reply to message #395355] Wed, 01 April 2009 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
SELECT page
subquery_factoring_clause section and follow the links.

Regards
Michel
Re: Extract Data within the Parenthesis [message #395367 is a reply to message #395355] Wed, 01 April 2009 04:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ygsunilkumar wrote on Wed, 01 April 2009 10:40
Why data is used in the from clause? Is that Table name?

In my example it is used as an alias.

I suggest you read up on some basic SQL before you continue. SQL can be very confusing if you try to dive in unprepared.
Re: Extract Data within the Parenthesis [message #395369 is a reply to message #395355] Wed, 01 April 2009 04:20 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The name used in the WITH clause can be used as a data source in the FROM clause.
Previous Topic: SQL - Escape character
Next Topic: regular expression (merged)
Goto Forum:
  


Current Time: Sun Dec 11 04:35:59 CST 2016

Total time taken to generate the page: 0.08262 seconds