SQL for Parsing a String Delimited with Periods 1

[adventurous1]

Hi all...

In a table, I have the following data stored in a field called DIRECTORY:

PDN.RAD.40391.0110.IA0
PDN.SW.69740.HDQ.IWB1
OMH.WDW.216942.Standard.119988
DCHH.HH.44463.OB.RR

I need to separate the characters between the periods into separate fields for the first three entries...

Example:

PDN.RAD.40391.0110.IA0

Field 1 = PDN
Field 2 = RAD
Field 3 = 40391

Can anyone help?

Thanks!

Ezekiel

 

[SantaMufasa]

Ezekiel,

In cases like this, I like to use my all-purpose, generic function called PARSE. The three arguments for the function are: (<string to parse>,<which occurence>,<delimiter>):

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.

Here is your sample data:

SQL> select * from adventurous;

DIRECTORY
-------------------------------
PDN.RAD.40391.0110.IA0
PDN.SW.69740.HDQ.IWB1
OMH.WDW.216942.Standard.119988
DCHH.HH.44463.OB.RR

And here is the invocation of the PARSE function that returns what you wanted:

col field1 format a10
col field2 format a10
col field3 format a10
select parse(directory,1,'.') field1
      ,parse(directory,2,'.') field2
      ,parse(directory,3,'.') field3
from adventurous
/

FIELD1     FIELD2     FIELD3
---------- ---------- ----------
PDN        RAD        40391
PDN        SW         69740
OMH        WDW        216942
DCHH       HH         44463

Let us know if this is what you wanted.

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

 

[adventurous1]

Thanks...

If I am just a regular 'ol database user and dont have privileges to do this with a procedure, is there a plain way of doing this with a SQL (or series of) SQL statements?

Thanks again.

 

[SantaMufasa]

In that case, you can use this less-elegant code:

select substr(directory,1,instr(directory,'.')-1) field1
      ,substr(directory,instr(directory,'.')+1
             ,instr(directory,'.',1,2)
             -instr(directory,'.',1,1)-1) field2
      ,substr(directory,instr(directory,'.',1,2)+1
             ,instr(directory,'.',1,3)
             -instr(directory,'.',1,2)-1) field3
from adventurous
/

FIELD1     FIELD2     FIELD3
---------- ---------- ----------
PDN        RAD        40391
PDN        SW         69740
OMH        WDW        216942
DCHH       HH         44463

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

 

[adventurous1]

Less elegant but worked like a charm.

Big thanks to Mufasa...!

 

[jimirvine]

You might want to look at regular expressions:

WITH T AS (SELECT 'PDN.RAD.40391.0110.IA0' info
FROM dual
UNION ALL
SELECT 'PDN.SW.69740.HDQ.IWB1'
FROM dual
UNION ALL
SELECT '
OMH.WDW.216942.Standard.119988'
FROM dual
)
SELECT t.info
, REGEXP_SUBSTR(t.info, '[^.]+', 1, 1) col1
, REGEXP_SUBSTR(t.info, '[^.]+', 1, 2) col2
, REGEXP_SUBSTR(t.info, '[^.]+', 1, 3) col3
, REGEXP_SUBSTR(t.info, '[^.]+', 1, 4) col4
, REGEXP_SUBSTR(t.info, '[^.]+', 1, 5) col5
FROM t;

 

[Thargy]

adventurous,

I'm sure your 'big thanks to Mufasa' were worthy of a star, so allow me to correct the situation and award him one on your behalf.

T

Grinding away at things Oracular

 

[SantaMufasa]

Very gracious of you, John. Thanks.

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