extracting part of a column value 1

[cheesyman5k]

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

 

[SantaMufasa]

Cheesy,

A popular function to parse out any item you want from amongst delimiters in a string is my "parse" function:

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:

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:

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.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

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:

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.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cheesyman5k]

Mufasa,

Man, you are remarkable, thanks so much!

cheese