string manipulation functions in stored procedure

[sourabhjha]

I have to write a stored procedure which takes a comma delimited string as input parameter and then inserts each of those string parts into a table..say incoming string is "10,20,30" then i need to insert 10,20 and 30 as three diff rows in a table.......so can can someone you tell me the syntax for string manipulation functions like substring,index of and all..or some site where i can get all these syntaxes....
and i also need the syntax for one one date functions something like getnextdate which gives me the next date given a date...
Thanks in advance
-Sourabh

 

[lewisp]

I wrote this function some time ago to extract specific parts of a delimited string. Enter the string, the column number you wish to extract and, optionally, the delimit character.

FUNCTION Fn_Get_Delimit (p_string IN VARCHAR2,
p_col IN NUMBER,
p_delimit IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
l_delimit1 NUMBER := 0;
l_delimit2 NUMBER := 0;
BEGIN
IF p_col > 1
THEN
l_delimit1 := Instr(p_string,p_delimit,1,p_col - 1);
END IF;
--
IF p_col > 0
THEN
l_delimit2 := Instr(p_string,p_delimit,1,p_col);
END IF;
--
IF p_col <= 1
THEN
IF l_delimit2 = 0
THEN
Return p_string;
ELSE
Return Substr(p_string,1,l_delimit2 - 1);
END IF;
--
ELSIF l_delimit1 = 0
THEN
Return p_string;
--
ELSIF l_delimit2 = 0
THEN
Return Substr(p_string,l_delimit1 + 1);
ELSE
Return Substr(p_string,l_delimit1 + 1,l_delimit2 - l_delimit1 - 1);
END IF;
END Fn_Get_Delimit;

 

[jee]

All of the string and date manipulation functions and other built-in functions are documented in the Oracle SQL manual. You can find this online at the Oracle Technet site, along with the rest of the manual set:

http://otn.oracle.com/docs/products/oracle8i/content.html

 

[Guest_imported]

Try this for your existing problem,
This code will handle any number of comma separations in a string. Replace DBMS statements with your INSERT Statements.

DECLARE
vMainStr VARCHAR2(100) := '10,20,30,40,50,60,70,80,90';
vColStr VARCHAR2(10) := '';
vPosn NUMBER := 0;
BEGIN
vPosn := Instr(vMainStr, ',', 1);
LOOP
EXIT WHEN vPosn = 0
vColStr := Substr(vMainStr, 1, vPosn - 1);
DBMS_OUTPUT.PUT_LINE(vColStr);
-- Put your INSERT Statement here
vMainStr := Substr(vMainStr, vPosn + 1);
vPosn := Instr(vMainStr, ',', 1);
END LOOP;
vColStr := vMainStr;
DBMS_OUTPUT.PUT_LINE(vColStr);
-- Put your INSERT Statement here
END;