RE: String manipulation

From: John Flack <JohnF_at_smdi.com>
Date: Tue, 27 Jan 2004 06:49:25 -0800
Message-ID: <F001.005DE342.20040127064925@fatcity.com>

Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I wrote a PL/SQL package with functions you can use for this. Find it = at http://www.smdi.com/employee/johnf/list.pks and = http://www.smdi.com/employee/johnf/list.pkb. I wrote it so that only = the first call parses the string. Subsequent calls use the already = parsed pieces.

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

Sent: Tuesday, January 27, 2004 9:29 AM
To: Multiple recipients of list ORACLE-L

select=20
substr(subject,1,instr(subject,'~')-1) first,=20 substr(subject,instr(subject,'~')+1, =
instr(subject,'~',1,2)-(instr(subject,'~'))-1) second,=20 substr(subject,instr(subject,'~',1,2)+1,length(subject)) third=20 from test_table=20
where test_column=3D1700455
/=20
=20
Ray

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

[mailto:Ronald.Stefick_at_RANDOLPH.AF.MIL]
Sent: Monday, January 26, 2004 11:29 PM
To: Multiple recipients of list ORACLE-L

I'm trying to separate a string into 3 values:=20 The string is:=20
mystr1~mystr2~mystr3=20

Here is the code so far:=20
  1 select substr(subject,1,instr(subject,'~')-1) first,=20   2 substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-1) = second,=20
  3 substr(subject,instr(subject,'~',1,2)+1,length(subject))=20   4 from test_table=20
  5 where test_column=3D1700455=20

The result I get is:=20
mystr1=20
mystr2~mystr3=20
mystr3=20

The result I want is:=20

mystr1=20
mystr2=20
Mystr3=20

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

_________________________________________________________________________=

__=20

This email and any attached to it are confidential and intended only for =

the individual or entity to which it is addressed. If you are not the =
intended recipient, please let us know by telephoning or emailing the =
sender. You should also delete the email and any attachment from your =

systems and should not copy the email or any attachment or disclose = their content to any other person or entity. The views expressed here = are not necessarily those of Churchill Insurance Group plc or its = affiliates or subsidiaries. Thank you.=20

Churchill Insurance Group plc. Company Registration Number - 2280426. = England.=20

Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 = 1DP.=20

------_=_NextPart_001_01C3E4E4.C5DEFC78

Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<TITLE>String manipulation</TITLE>

<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D829364314-27012004><FONT face=3DArial color=3D#0000ff =

size=3D2>I=20
wrote a PL/SQL package with functions you can use for this.  Find = it=20
at <A=20
href=3D"http://www.smdi.com/employee/johnf/list.pks">http://www.smdi.com/= employee/johnf/list.pks</A>=20
and  <A=20
href=3D"http://www.smdi.com/employee/johnf/list.pkb">http://www.smdi.com/= employee/johnf/list.pkb</A>. =20
I wrote it so that only the first call parses the string.   = Subsequent=20
calls use the already parsed pieces.</DIV> <BLOCKQUOTE>
 <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft>-----Original Message----- From: Feighery Raymond=20 [mailto:Raymond.Feighery_at_churchill.com] Sent: Tuesday, = January 27,=20
 2004 9:29 AM To: Multiple recipients of list=20 ORACLE-L Subject: RE: String = manipulation </DIV>
 <DIV>select </DIV> <DIV>substr(subject,1,instr(subject,'~')-1) first,=20 </DIV>
 <DIV>substr(subject,instr(subject,'~')+1,=20 instr(subject,'~',1,2)-(instr(subject,'~'))-1) second,=20 </DIV>
 <DIV>substr(subject,instr(subject,'~',1,2)+1,length(subject)) =

third </DIV>
 <DIV>from=20
 test_table </DIV> <DIV>where test_column=3D1700455</DIV> <DIV>/ </DIV>

  <DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial color=3D#0000ff size=3D2>Ray</FONT></DIV>
  <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">

<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT = face=3DTahoma=20

size=3D2>-----Original Message----- From: Stefick Ronald S = Contr=20

ESC/HRIDA [mailto:Ronald.Stefick_at_RANDOLPH.AF.MIL] Sent: = Monday,=20

January 26, 2004 11:29 PM To: Multiple recipients of list=20 ORACLE-L Subject: String manipulation </DIV> I'm trying to separate a string into = 3=20

values: The string is: = <FONT=20

face=3DArial size=3D2>mystr1~mystr2~mystr3 Here is the code so far: = <FONT=20

face=3DArial size=3D2>  1  select=20 substr(subject,1,instr(subject,'~')-1) first, <FONT = face=3DArial=20

size=3D2>  2  substr(subject,instr(subject,'~')+1,=20 instr(subject,'~',1,2)-1) second,  =20

3  =
substr(subject,instr(subject,'~',1,2)+1,length(subject))=20

4  from = test_table <FONT=20

face=3DArial size=3D2>  5  where = test_column=3D1700455

The result I get is: <FONT = face=3DArial=20

size=3D2>mystr1 mystr2~mystr3=20

    <BR><FONT face=3DArial size=3D2>mystr3</FONT> </P>
    <P><FONT face=3DArial size=3D2>The result I want is:</FONT> =
<BR><FONT face=3DArial=20

size=3D2>mystr1 mystr2 = <FONT=20

face=3DArial size=3D2>Mystr3 TIA, Scott Stefick <FONT = face=3DArial=20

size=3D2>MILPDS OCP Oracle DBA scott.stefick_at_randolph.af.mil <FONT face=3DArial =

size=3D2>210-565-2540 </BLOCKQUOTE> <FONT face=3DArial=20
=

size=3D2>________________________________________________________________=

___________=20
 
 This email and any attached to it are = confidential=20
 and intended only for the individual or entity to which it is = addressed. If=20
 you are not the intended recipient, please let us know by telephoning = or=20
 emailing the sender. You should also delete the email and any = attachment from=20
 your systems and should not copy the email or any attachment or = disclose their=20
 content to any other person or entity. The views expressed here are = not=20
 necessarily those of Churchill Insurance Group plc or its affiliates = or=20
 subsidiaries. Thank you. Churchill Insurance Group plc. Company = Registration=20
 Number - 2280426. England. Registered Office: Churchill Court, = Westmoreland=20
 Road, Bromley, Kent BR1 1DP. =
 </BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C3E4E4.C5DEFC78--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Flack
  INET: JohnF_at_smdi.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 Tue Jan 27 2004 - 08:49:25 CST