Thinking Out Loud

November 17, 2016

Playing with SUBSTR and INSTR

Filed under: oracle — mdinh @ 7:18 am
hawk:(SYS@hawk):PRIMARY> r
  1  SELECT handle,
  2  SUBSTR(handle, INSTR(handle,'/',-1)+1)                extract_last_field,
  3  SUBSTR(handle, 1, (INSTR(handle,'/',-1,1)-1))         remove_last_field,
  4  SUBSTR(handle, 1, (INSTR(handle,'/',1,2))-1)          extract_first_field,
  5  SUBSTR(handle, INSTR(handle,'/',1,2), length(handle)) remove_first_field
  6* FROM v$backup_piece_details

HANDLE
--------------------------------------------------------------------------------
EXTRACT_LAST_FIELD
--------------------------------------------------------------------------------
REMOVE_LAST_FIELD
--------------------------------------------------------------------------------
EXTRACT_FIRST_FIELD
--------------------------------------------------------------------------------
REMOVE_FIRST_FIELD
--------------------------------------------------------------------------------
/oradata/backup/HAWK_3183859104_20161116_24rl3690_1_1.bsu
HAWK_3183859104_20161116_24rl3690_1_1.bsu
/oradata/backup
/oradata
/backup/HAWK_3183859104_20161116_24rl3690_1_1.bsu

/oradata/backup/HAWK_3183859104_20161116_1vrl35ev_2_1.bkp
HAWK_3183859104_20161116_1vrl35ev_2_1.bkp
/oradata/backup
/oradata
/backup/HAWK_3183859104_20161116_1vrl35ev_2_1.bkp

hawk:(SYS@hawk):PRIMARY> r
  1  SELECT name,
  2  SUBSTR(name, INSTR(name,'/',-1)+1)              extract_last_field,
  3  SUBSTR(name, 1, (INSTR(name,'/',-1,1)-1))       remove_last_field,
  4  SUBSTR(name, 1, (INSTR(name,'/',1,2))-1)        extract_first_field,
  5  SUBSTR(name, INSTR(name,'/',1,2), length(name)) remove_first_field
  6* FROM v$datafile

NAME
--------------------------------------------------------------------------------
EXTRACT_LAST_FIELD
--------------------------------------------------------------------------------
REMOVE_LAST_FIELD
--------------------------------------------------------------------------------
EXTRACT_FIRST_FIELD
--------------------------------------------------------------------------------
REMOVE_FIRST_FIELD
--------------------------------------------------------------------------------
/oradata/SGN_HAWK/datafile/o1_mf_system_d00pchld_.dbf
o1_mf_system_d00pchld_.dbf
/oradata/SGN_HAWK/datafile
/oradata
/SGN_HAWK/datafile/o1_mf_system_d00pchld_.dbf

/oradata/SGN_HAWK/datafile/o1_mf_sysaux_d00pdw4l_.dbf
o1_mf_sysaux_d00pdw4l_.dbf
/oradata/SGN_HAWK/datafile
/oradata
/SGN_HAWK/datafile/o1_mf_sysaux_d00pdw4l_.dbf

hawk:(SYS@hawk):PRIMARY>
Advertisements

1 Comment »

  1. Handy and helful

    Thank you

    Comment by AnuragDBA — November 17, 2016 @ 2:57 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: