parsed pieces.
-Original Message-From: Feighery Raymond
[mailto:[EMAIL PROTECTED]Sent: Tuesday, January 27,
2004 9:29 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: String manipulation
select
substr(subject,1,instr(subject,'~')-1) first,
substr(sub
Title: String manipulation
select
substr(subject,1,instr(subject,'~')-1) first,
substr(subject,instr(subject,'~')+1,
instr(subject,'~',1,2)-(instr(subject,'~'))-1) second,
substr(subject,instr(subject,'~',1,2)+1,length(subject))
third
from
test_table
where
test_column=1700455
/
Ray
On 01/26/2004 06:29:26 PM, Stefick Ronald S Contr ESC/HRIDA wrote:
I'm trying to separate a string into 3 values:
The string is:
mystr1~mystr2~mystr3
There is trivial, non-PL/SQL solution based on the split
function. To see more, type "perldoc -f split" and you should
see the light.
--
Please see
Title: Message
If you
have a way to work this out in shell then there is a simpler solution
...
$
export VAR='mystr1~mystr2~mystr3'$ echo $VARmystr1~mystr2~mystr3$
echo $VAR | tr '~'
'\012'mystr1mystr2mystr3$
HTH,
Nikhil
-Original Message-From: Stefick Ronald S Contr
ESC/HRIDA
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
Title: Message
Substr(''mystr1~mystr2~mystr3', 1, 20) => 1 is the position and 20 the
length (not the position). The "substring" functions return a portion of
string, beginning at character position, substring_length characters
long.
SELECT
substr('mystr1~mystr2~mystr3',1,instr('mystr1~myst
