Home » SQL & PL/SQL » SQL & PL/SQL » parse a stirng
parse a stirng [message #187291] Fri, 11 August 2006 12:55 Go to next message
dallascowboy99
Messages: 3
Registered: August 2006
Junior Member
can anyone help me with some code example of how to do parsing based on this output..PLEASE

parse_string('123', country club dr, haywo0d, ca, 95769', n);
if n=1 then output = 123
if n=2 then output = country club dr
if n=3 then output = haywood
and so on based on the lenght of the string.

thanks

DCB

[Updated on: Fri, 11 August 2006 12:57]

Report message to a moderator

Re: parse a stirng [message #187297 is a reply to message #187291] Fri, 11 August 2006 14:00 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this might help:
CREATE OR REPLACE FUNCTION parse (par_str IN CHAR, par_n IN NUMBER) 
  RETURN CHAR 
IS
  SUBSTR VARCHAR2(100);
BEGIN
  SELECT SUBSTR(par_str, 
                INSTR(par_str, ',', 1, par_n - 1) + 1,
                INSTR(par_str, ',', 1, par_n) - INSTR(par_str, ',', 1, par_n - 1) - 1
               )
  INTO SUBSTR
  FROM dual;
                    
  RETURN trim(SUBSTR);
END;
/
It is used as
SELECT parse('123, country club dr, haywo0d, ca, 95769', 4) FROM dual;
Re: parse a stirng [message #187300 is a reply to message #187297] Fri, 11 August 2006 14:10 Go to previous message
dallascowboy99
Messages: 3
Registered: August 2006
Junior Member
Thanks a lot little foot...
Previous Topic: simple SQL.. but takes forever to return the result.
Next Topic: finding letters in an alphanumeric
Goto Forum:
  


Current Time: Sat Dec 03 00:53:54 CST 2016

Total time taken to generate the page: 0.08726 seconds