Breaking up a string

[IanWaterman]

Due to lazy design on a customer input form, client name is only entered as a single field. I have been assured it will always be entered as

Title Firstname Surname

I need to build a means of breaking this field into 3 separate fields in a View.

Is there a function within Oracle that I can use, or does any one know the SQL I can use to build a function that can do this for me.

Thank you

Ian

 

[Dagon]

Instr and substr. Search the forum and you will find numerous examples.

 

[SantaMufasa]

Ian,

Dagon's suggestion (to use Instr and Substr) is right on.

Is there a function within Oracle that I can use...?

Although Oracle does not have a built-in function, you are welcome to use my user-defined function named "parse". I'll illustrate its use, below.


Section 1 -- Some sample data:

select * from waterman;

CLIENT_NAME
--------------------
Mr. Ian Waterman
Mr. David Hunt
Mrs. Sarah Donaldson

Section 2 -- Splitting of CLIENT_NAME into three distinct columns using the "parse" function:

col title format a20
col firstname format a20
col surname format a20
select parse(client_name,1,' ') Title
      ,parse(client_name,2,' ') Firstname
      ,parse(client_name,3,' ') Surname
  from Waterman;

TITLE                FIRSTNAME            SURNAME
-------------------- -------------------- ------------------
Mr.                  Ian                  Waterman
Mr.                  David                Hunt
Mr.                  Sarah                Donaldson

Notice in the above invocation of "parse", the format is: "parse(<expression>,<which occurrence>,<delimiter>)". The meanings of the arguments are:[ul][li]<expression> - The string to parse.[/li][li]<which occurrence> - The <nth> occurrence string you want from <expression> based upon <delimiter>.[/li][li]<delimiter> - The character or string you want to use to separate sub-strings from within <expression>.[/li][/ul]

Section 3 -- The code for the "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;

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]

 

[IanWaterman]

I did something similar to your function but just using Instr and substr as suggested by Dagon.

It was more complicated as users enter absolutely any thing they want into this field. Its not strictly their fault as lazy design has allowed them to do it. Form should have title, first name and last name fields.

I have data like

Mr John Smith
Mr Smith
John Smith
John

Further complicated by fact that Title for say Mr has been entered as

'Mr', 'Mr.', 'mr', 'MR' hence my title filter in the query.

This is my attempt at code it generally works except where/when I have not anticipated a new title. Welcome any solutions which can make it more robust.

select Client_key, cli_post_salu,
(case
when instr(trim(cli_post_salu), ' ', 1) =0 then Null
when substr(cli_post_salu, 1, (instr(cli_post_salu, ' ', 1)-1)) in ('DR', 'Dr', 'Dr.', 'MR', 'MRs', 'Miss', 'Mis', 'Mr.', 'Mr', 'Mr/Mrs', 'Mr/Miss', 'Mrd', 'Mrs', 'Mrs.', 'Ms', 'Nr', 'miss', 'mr', 'mrs', 'ms', '|Mr')
then substr(cli_post_salu, 1, (instr(cli_post_salu, ' ', 1)-1)) else Null end ) TITLE,
(case
when instr(trim(cli_post_salu), ' ', 1) = 0 then cli_post_salu
when not(substr(cli_post_salu, 1, (instr(cli_post_salu, ' ', 1)-1)) in ('DR', 'Dr', 'Dr.', 'MR', 'MRs', 'Miss', 'Mis', 'Mr.', 'Mr', 'Mr/Mrs', 'Mr/Miss', 'Mrd', 'Mrs', 'Mrs.', 'Ms', 'Nr', 'miss', 'mr', 'mrs', 'ms', '|Mr'))
then substr(cli_post_salu, 1, (instr(cli_post_salu, ' ', 1)-1))
when substr(cli_post_salu, 1, (instr(cli_post_salu, ' ', 1)-1)) in ('DR', 'Dr', 'Dr.', 'MR', 'MRs', 'Miss', 'Mis', 'Mr.', 'Mr', 'Mr/Mrs', 'Mr/Miss', 'Mrd', 'Mrs', 'Mrs.', 'Ms', 'Nr', 'miss', 'mr', 'mrs', 'ms', '|Mr')
and instr(trim(cli_post_salu), ' ', 1, 2) <> 0
then substr(cli_post_salu, (instr(cli_post_salu, ' ', 1) +1), (instr(cli_post_salu, ' ', 1, 2)-(instr(cli_post_salu, ' ', 1) +1)))
Else Null End) FIRST_NAME,
(case
when instr(trim(cli_post_salu), ' ', 1) = 0 then null
when instr(Trim(cli_post_salu), ' ', 1, 2) = 0 then substr(cli_post_salu, (instr(cli_post_salu, ' ', 1) +1), length(cli_post_salu))
when instr(cli_post_salu, ' ', 1, 2) <> 0 then substr(cli_post_salu, (instr(cli_post_salu, ' ', 1, 2) +1), length(cli_post_salu))
else Null end) LAST_NAME
from client
where cli_post_salu is not null

Thanks

Ian

 

[SantaMufasa]

I have been assured it will always be entered as

Title Firstname Surname

So, when your specifiers asserted the above, they lied, right? <grin>


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

 

[SantaMufasa]

Also, Ian, I cannot recommend highly enough the importance of "Mufasa's First Rule of Quality Data":

Let bad data live as short a life as possible.

IMO, rather than enabling and perpetuating bad data entry by building code that transforms your single-string bad data into three-string good data, you should modify your application to split the name into its three desired fields upon data entry, and do a one-time scrubbing and parsing of your single-column bad data into its appropriate three-column good data.

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

 

[IanWaterman]

To answer your questions - Yes they lied!!

And yes you are right the data should be cleansed, but my client wants a quick and dirty solution now. So I will have to use my clunky code until someone with a higher grade than me insists it is sorted out properly.

I will keep your Parsing function for future use as I am sure someone else will have the same problem.

Thanks for your help.

Ian