Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

extracting part of a column value 1

Status
Not open for further replies.

cheesyman5k

Programmer
Oct 19, 2007
9
US
I have a string with pipes as in the following that I need to extract multiple pieces of data from.

12345|1600 Pennsylvania|09999|01876|2125559999

it contains an id, address, second id, zipcode and lastly telephone number

but sometimes the value is like this

'|||01876|2125559999'

and i need to extract the zipcode only

i am having a hard time with this, thanks in advance
 
Cheesy,

A popular function to parse out any item you want from amongst delimiters in a string is my "parse" function:
Code:
create or replace function parse (str_in varchar2, which number, delimiter varchar2)
    return varchar2
is
    delim_loc       number;
    prev_beg        number;
    ret_str         varchar2(4000);
begin
    if which < 1 then
        return null;
    end if;
    delim_loc := instr(str_in,delimiter,1,which);
    if delim_loc = 0 then
        delim_loc := length(str_in)+1;
    end if;
    if which = 1 then
        prev_beg := 1;
    else
        prev_beg := instr(str_in,delimiter,1,which-1)+1;
    end if;
    ret_str := substr(str_in,prev_beg,delim_loc-prev_beg);
    if length(ret_str) = length(str_in) then
        if which = 1 then
            return ret_str;
        else
            return null;
        end if;
    else
        return ret_str;
    end if;
end;
/

Function created.
The invocation of the function is:
Code:
parse(<expression to parse>,<which nth item to return>,<delimiter>)
So, in your case, if you want just the zip code from your sample data, a query could be:
Code:
select * from cheesy;

CONTACT_POINTS
----------------------------------------------
12345|1600 Pennsylvania|09999|01876|2125559999
|||01876|2125559999

select parse(contact_points,4,'|') zipcode from cheesy;

ZIPCODE
---------
01876
01876
Let us know if this is useful.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
BTW, Cheesy, if you do not fancy using my user-defined function ("parse") to parse out ZIPCODEs, a method of doing so with strictly native Oracle built-in functions is:
Code:
select substr(contact_points,instr(contact_points,'|',1,3)+1,
       instr(contact_points,'|',1,4)-
       instr(contact_points,'|',1,3)-1) zipcode
  from cheesy;

ZIPCODE
-------
01876
01876
Again, let us know your preference.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top