Solved

Extracting Part Of SQL Output

Posted on 2009-07-07
3
230 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
  • 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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to do this MS SQL script? 11 48
STDEVP in SQL 2 34
SQL Query Syntax Error 9 32
Convert char to decimal in a SQL Server View 14 12
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now