Home » SQL & PL/SQL » SQL & PL/SQL » Split string problem
Split string problem [message #248676] Sat, 30 June 2007 05:23 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member

Hi !!

There is any function in oracle like cut or split function in other language(perl,shell).
Let say ,one string ‘Yash|Mishra’. Now I want to cut this string on pipe(‘|’) value and store the result in variable. Some thing like that

Declare  
V_firstname varchar2 (30);
V_lastname varchar2 (30);
V_string varchar2(60) := ‘Yash|Mishra’;

Begin 

(V_firstname, V_lastname) := split(‘|’, V_string);

Dbms.output.put_line (‘firstname’||V_firstname|||chr(10)||’last_name’|| V_lastname);

End;


--Yash
Re: Split string problem [message #248690 is a reply to message #248676] Sat, 30 June 2007 09:29 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Look at manual for INSTR and SUBSTR functions.

Michael

[Updated on: Sat, 30 June 2007 09:30]

Report message to a moderator

Re: Split string problem [message #248692 is a reply to message #248676] Sat, 30 June 2007 10:16 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Some of the techniques at SQL Snippets: SQL Techniques Tutorials - String to Columns may help you.

--
Joe Fuda
SQL Snippets
Re: Split string problem [message #248800 is a reply to message #248692] Mon, 02 July 2007 01:18 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Michle!! as per my understanding i can use INSTR and SUBSTR in fallowing manner.

Declare  
V_firstname varchar2 (30) ;
V_middle varchar2 (30) ;
V_lastname varchar2 (30) ;
V_string varchar2(60) := 'Yash|Kumar|Mishra';

Begin 

V_firstname := substr(V_string,0,INSTR(V_string,'|',1)-1) ;
V_middle := substr(V_string,INSTR(V_string,'|')+1,INSTR(V_string,'|',INSTR(V_string,'|',1))) ;
 
Dbms_output.put_line ('V_firstname '||V_firstname ||V_middle||'V_middle');

End;



Can u suggest me how can I get both (first name, middle name) in one shot...

--Yash
Re: Split string problem [message #248801 is a reply to message #248800] Mon, 02 July 2007 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you ask how to set 2 variables at the same time?
Answer is the same as how to do i:=1 and j:=2 in one statement.
Can you do this?

Regards
Michel
Re: Split string problem [message #248804 is a reply to message #248801] Mon, 02 July 2007 01:38 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Yes i am asking same thing ..is it possible in oracle if yes then how can i do that ..




Re: Split string problem [message #248806 is a reply to message #248804] Mon, 02 July 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What the interest of this but obfuscate the code?

Regards
Michel
Re: Split string problem [message #248809 is a reply to message #248806] Mon, 02 July 2007 01:54 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Nothing Michle ..i am juist doing R&D ..is it possible ?
Re: Split string problem [message #248811 is a reply to message #248809] Mon, 02 July 2007 02:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

To prevent from correction, I add "unless you use such stupid thing like switching to SQL".

Regards
Michel

[Updated on: Mon, 02 July 2007 02:01]

Report message to a moderator

Previous Topic: wrap error in oracle 9i not in 10g
Next Topic: Function need tuning
Goto Forum:
  


Current Time: Sat Dec 03 17:55:45 CST 2016

Total time taken to generate the page: 0.09709 seconds