Solved

Extracting Part Of SQL Output

Posted on 2009-07-07
3
235 Views
Last Modified: 2013-12-19
Hello,

The displayed SQL will output the below entry:

LOCATION=c:\oradata\DB1\recovery1

Q:
How can I just output everything to the right of the equals sign?

Desired output:  c:\oradata\DB1\recovery1

Thanks,

Michael

SELECT value 
FROM v$parameter 
WHERE name='log_archive_dest_1';

Open in new window

0
Comment
Question by:michael4606
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
Andytw earned 450 total points
ID: 24793523
This should do it:
SELECT SUBSTR(value, INSTR(value,'=') + 1 )
FROM v$parameter 
WHERE name='log_archive_dest_1';

Open in new window

0
 
LVL 11

Assisted Solution

by:Andytw
Andytw earned 450 total points
ID: 24793789
Just as a follow-up.  I see you're looking at the parameter log_archive_dest_n.  The format of this parameter doesn't necessarily *just* contain LOCATION="????", it consists of name/value pairs. So the value of this parameter could be, for example, 'LOCATION="e:\test\" OPTIONAL REOPEN=10.

In which case, something a bit more advanced is needed which can strip out just the location, if that's all you're interested in ...
SELECT regexp_replace(value,'LOCATION=(.+? ).*', '\1', 1,0,'i')
FROM v$parameter 
WHERE name='log_archive_dest_1';

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 50 total points
ID: 24794544
Not sure what you intend to do with that result, but note that the value can also be null, so you may want to wrap an "nvl(...,default_destination_path) around Andytw's regular expression query.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question