RE: Selecting a slash '/' from a varchar data field

From: Ken Naim <kennaim_at_gmail.com>
Date: Tue, 25 Jul 2006 13:19:40 -0500
Message-ID: <00b801c6b016$e5998350$b4ae6a44@KenHome>

If I am understanding the question just make it substr(file_name,22)

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rodd Holman
Sent: Tuesday, July 25, 2006 11:30 AM
To: oracle-l_at_freelists.org
Subject: Selecting a slash '/' from a varchar data field

DB: 9.2.0.5, OS: HP-UX 11.11

I'm working on some size monitoring scripts to integrate with OS disk usage on HP_UX. The problem I'm running into is that I want to do a group by filesystem. Oracle doesn't record this discrete of information.

My goal is to be able to spool a df and sql output into a file that I can then pull into OpenOffice Calc and verify to make sure that my files are properly adjusted to not exceed 100% of filesystem when extended (maxbytes). It's also my ammo to get SA's to fork over more disk when I need it.

The file_name in dba_data_files contains the filesystem inclusive.

Here's what I'm doing and what I want to parse:

select * from dba_data_files order by file_name;

FILE_NAME

---

/oradata/lgarsprd_dmx/archive/lgars_arch_01.dbf ...

the key identifier here for me is 'archive'

select rtrim(substr(file_name, 23),'/') from dba_data_files order by file_name;

RTRIM(SUBSTR(FILE_NAME,23),'/')

---

archive/lgars_arch_01.dbf
...

Running this on my db will give me every file. I only want to limit it to the 'archive', 'index', etc. So I can do a sum of the bytes, maxbytes and group on the filesystem identifier.

Is there any way to get Oracle to let me select the slash? Escape key sequence or regular expression?

Thanks in advance

Rodd
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jul 25 2006 - 13:19:40 CDT