Re: SQL Question

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Wed, 29 May 2002 12:31:43 -0800
Message-ID: <F001.0046EB0E.20020529123143@fatcity.com>

"Deshpande, Kirti" wrote:
>
> Thanks.
> Substr/instr was rejected because it was a bit difficult to read the code.
>
> Also, they wanted to extract the fields in their own column headings (new
> requirement). So 'replace' may not fly much !!
>
> - Kirti
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 1:45 PM
> To: Multiple recipients of list ORACLE-L
>
> What about
>
> select commission_id, replace(com_text_msg,'~',chr(9))
> from tab1
>
> which would work if going to a tab separated file for something like excel.
>
> Whats wrong with substr/instr?
>
> Iain Nicoll
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 7:22 PM
> To: Multiple recipients of list ORACLE-L
>
> I need some help...
>
> The database table has following structure.
> commision_id number
> com_text_msg varchar2(500)
>
> The second column contains data fields that are delimited by ~ and
> delimiter's position varies. But there are only eight data fields in the
> column.
>
> Is there a way in SQL, other than substr/instr combinations, to extract each
> data field to report?
>
> Thanks.
>
> - Kirti
>

Kirti,

   I had a vague remembrance of something which might have been helpful in dbms_utility but the closer you get to is a comma_to_table() procedure. Even without your reluctance to REPLACE(), you would be lucky if your data could bear this kind of transformation.   Well, its a bit like sweeping dirt under the carpet, but I think that a function

                 getfield(string in varchar2, pos in number, separator
in char)
                 return varchar2;

 full of substr and instr should make the upper level query more legible ...  

Regards,

Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed May 29 2002 - 15:31:43 CDT