Re: String manipulation

From: <Jared.Still_at_radisys.com>
Date: Mon, 26 Jan 2004 16:44:25 -0800
Message-ID: <F001.005DE28F.20040126164425@fatcity.com>

Content-Type: text/plain; charset="us-ascii"

Here is an example for you.

You might want to spend some more time studying the instr() function in the SQL
manual to understand how this works. :)

define t = 'mystr1~mystr2~mystr3'

var t varchar2(30)

begin

select '&&t' into :t from dual;
end;
/

select

   substr(:t,1,instr(:t,'~')-1) t1
   , substr(:t,instr(:t,'~')+1, instr(:t,'~')-1) t2
   , substr(:t,instr(:t,'~',instr(:t,'~')+1)+1, instr(:t,'~')-1) t2

from dual
/

or the somewhat simpler:

select

   substr(:t,1,instr(:t,'~')-1) t1
   , substr(:t,instr(:t,'~')+1, instr(:t,'~')-1) t2
   , substr(:t,instr(:t,'~',1,2)+1, instr(:t,'~')-1) t2

from dual
/

HTH Jared

Stefick Ronald S Contr ESC/HRIDA <Ronald.Stefick_at_RANDOLPH.AF.MIL> Sent by: ml-errors_at_fatcity.com
01/26/2004 03:29 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        String manipulation

I'm trying to separate a string into 3 values: The string is:
mystr1~mystr2~mystr3
Here is the code so far:
  1 select substr(subject,1,instr(subject,'~')-1) first,   2 substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-1) second,
  3 substr(subject,instr(subject,'~',1,2)+1,length(subject))   4 from test_table
  5 where test_column=1700455
The result I get is:
mystr1
mystr2~mystr3
mystr3
The result I want is:
mystr1
mystr2
Mystr3
TIA,
Scott Stefick
MILPDS OCP Oracle DBA
scott.stefick_at_randolph.af.mil
210-565-2540

--=_alternative 000402B488256E28_=
Content-Type: text/html; charset="us-ascii"

 Here is an example for you.
 
 You might want to spend some more time studying the instr() function in the SQL 
 manual to understand how this works.  :)
 
 
 define t = 'mystr1~mystr2~mystr3'
 
 var t varchar2(30)
 
 begin
    select '&&t' into :t from dual;
 end;
 /
 
 select
    substr(:t,1,instr(:t,'~')-1) t1
    , substr(:t,instr(:t,'~')+1, instr(:t,'~')-1) t2
    , substr(:t,instr(:t,'~',instr(:t,'~')+1)+1, instr(:t,'~')-1) t2
 from dual
 /
 
 or the somewhat simpler:
 
 select
    substr(:t,1,instr(:t,'~')-1) t1
    , substr(:t,instr(:t,'~')+1, instr(:t,'~')-1) t2
    , substr(:t,instr(:t,'~',1,2)+1, instr(:t,'~')-1) t2
 from dual
 /
 
 HTH
 
 Jared
 

 
 
 
<table width=100%>
<tr valign=top>
<td>
<td>Stefick Ronald S Contr ESC/HRIDA <Ronald.Stefick_at_RANDOLPH.AF.MIL>
 Sent by: ml-errors_at_fatcity.com
 01/26/2004 03:29 PM
  Please respond to ORACLE-L
 
<td>        
         To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
         cc:        
         Subject:        String manipulation</table>
 
 
 I'm trying to separate a string into 3 values: 
The string is: mystr1~mystr2~mystr3 
Here is the code so far: 

 &nbsp;1 &nbsp;select substr(subject,1,instr(subject,'~')-1) first,</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>
 &nbsp;2 &nbsp;substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-1) second,</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>
 &nbsp;3 &nbsp;substr(subject,instr(subject,'~',1,2)+1,length(subject))</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>
 &nbsp;4 &nbsp;from test_table</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>
 &nbsp;5 &nbsp;where test_column=1700455</font><font size=3 face="Times New Roman"> </font>

The result I get is: 

mystr1</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>
mystr2~mystr3</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>
mystr3</font><font size=3 face="Times New Roman"> </font>


<p><font size=2 face="Arial">The result I want is:</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>

mystr1</font><font size=3 face="Times New Roman"> </font><font size=2 face="Arial"><br>

mystr2 Mystr3 
TIA, 
Scott Stefick 
MILPDS OCP Oracle DBA scott.stefick_at_randolph.af.mil 210-565-2540 



--=_alternative 000402B488256E28_=--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Mon Jan 26 2004 - 18:44:25 CST