Home » SQL & PL/SQL » SQL & PL/SQL » Selecting only part of a string (10204)
Selecting only part of a string [message #445086] Thu, 25 February 2010 16:05 Go to next message
justij
Messages: 3
Registered: February 2010
Junior Member
I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:

SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID

However, I only need the DATABASE piece selected in my FOR loop below.

CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;

BEGIN

FOR c1_rec IN c1 LOOP


END LOOP;

END;

Thank you for any help.
Re: Selecting only part of a string [message #445087 is a reply to message #445086] Thu, 25 February 2010 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
> The column contains backup job names that are formatted like the following:

>SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID

Absolutely poor design. You are now paying the price for the bad design.

>However, I only need the DATABASE piece selected in my FOR loop below.

use INSTR() & SUBSTR() functions

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

[Updated on: Thu, 25 February 2010 17:03]

Report message to a moderator

Re: Selecting only part of a string [message #445112 is a reply to message #445086] Thu, 25 February 2010 23:05 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select 'SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID' mycol from dual)
  2  select substr(mycol,
  3                instr(mycol,'_')+1,
  4                instr(mycol,'_',1,2)-instr(mycol,'_')-1
  5               ) db
  6  from data
  7  /
DB
--------
DATABASE

1 row selected.

SQL> with data as (select 'SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID' mycol from dual)
  2  select regexp_replace(mycol,'^[^_]+_([^_]+)_.*$','\1') db
  3  from data
  4  /
DB
--------
DATABASE

1 row selected.

Regards
Michel
Previous Topic: examination seating plan
Next Topic: Week problem.
Goto Forum:
  


Current Time: Fri Sep 30 03:48:06 CDT 2016

Total time taken to generate the page: 0.13481 seconds