OraFAQ Forum: Forms » Splitting a string by the delimiter

Home » Developer & Programmer » Forms » Splitting a string by the delimiter

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Splitting a string by the delimiter [message #82666]

Fri, 20 June 2003 01:28

Victoria
Messages: 152
Registered: July 2002

Senior Member

Hi,
Is there any function available in oracle to split a string by the delimiter.
i.e. If the string is
,,NETHERLANDSANTILLES ,0.366,0.0122,0.366,0.0122,0.366,0.0122
--I want to split the above string by commas.
(The number of commas might be varying in the real case)

Thanx in advance
~V~

Report message to a moderator

Re: Splitting a string by the delimiter [message #82683 is a reply to message #82666]

Mon, 23 June 2003 00:47

Shailender Mehta
Messages: 49
Registered: June 2003

Member

I have a 10-15 lines script which does what is required.

Test Db>@t
Input truncated to 1 characters
Field Value =
Field Value =
Field Value = NETHERLANDSANTILLES
Field Value = 0.366
Field Value = 0.0122
Field Value = 0.366
Field Value = 0.0122
Field Value = 0.366
Field Value = 9.0999
#Number of columns := 9

PL/SQL procedure successfully completed.

Test Db>ed t

Test Db>@t
Input truncated to 1 characters
Field Value =
Field Value =
Field Value = NETHERLANDSANTILLES
Field Value = 0.366
Field Value = 0.0122
Field Value = 0.366
Field Value = 0.0122
Field Value = 0.366
Field Value = 9.0999
Field Value = 10.1234
#Number of columns := 10

PL/SQL procedure successfully completed.

CODE
-------------------------------------------------------
Set ServerOutput On Size 999999;

Declare

PROCEDURE getColValue ( pInStr In Varchar2 ) IS
iColVal Varchar2(1000);
iFldPosStart number;
iFldPosEnd number;
iStrLen number;
iNumCols number;
BEGIN
iFldPosStart := 1;
iFldPosEnd := 1;
iStrLen := length(pInStr);
iNumCols := 0 ;

while (iFldPosEnd < iStrLen and iFldPosEnd > 0) loop
iFldPosEnd := instr(pInStr,',',iFldPosStart,1);
if iFldPosEnd != 0 then
iColVal := substr(pInStr,iFldPosStart,(iFldPosEnd - iFldPosStart ));
Dbms_Output.Put_Line ('Field Value = ' || iColVal);

iNumCols := iNumCols + 1;
iFldPosStart := iFldPosEnd + 1;
end if;
end loop;

iColVal := substr(pInStr, iFldPosStart);
Dbms_Output.Put_Line ('Field Value = ' || iColVal);

iNumCols := iNumCols + 1;
Dbms_Output.Put_Line ('#Number of columns := ' || To_Char(iNumCols));

end;

Begin
getColValue(',,NETHERLANDSANTILLES ,0.366,0.0122,0.366,0.0122,0.366,9.0999,10.1234');
End;
/

Report message to a moderator

Previous Topic:	Ranking report
Next Topic:	ActiveX control Library (*.dll)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 02:08:35 CDT 2024