Spliting a Name up into 3 columns 2

[fmrock]

I have a field that is called T1.Name.

This field is has the name in the format Doe, John M

I need to display this in a select as 3 separate fields.

Last_Name
First_Name
MI

Any help would be great.

Thanks.

 

[fmrock]

I was able to split up the Last and First Names like this...

But I still need to get the MI off of the first name and into its own column.

	TRIM(substr(AI.AIINSURED, 1, instr(AI.AIINSURED, ',', 1, 1)-1)) "Subscriber_Last_Name",

	TRIM(substr(AI.AIINSURED, instr(AI.AIINSURED, ',', 1, 1)+1, length(AI.AIINSURED))) "Subscriber_First_Name",

 

[smbpalepilsen]

SELECT SUBSTR(name, 1,INSTR(name,',') -1) last_name,
SUBSTR(name, INSTR(name,',') + 2, LENGTH(TRIM(name)) - (INSTR(name,',') + 3)) first_name,
SUBSTR(name,-1) mid_init
from table_name

 

[fmrock]

Thanks, But what if the MI is not always there?

 

[SantaMufasa]

FMRock,

(Note: This is a classic case of the database analyst/designer failing in her/his job, to identify that the business needs to store separately the last_name, first_name, and the Mid_init.)

Here is a revision to smbpalepilsen's code that deals with cases where there is no middle initial:

SELECT name Full_name
      ,SUBSTR(name, 1,INSTR(name,',') -1) last_name
      ,SUBSTR(name
             ,INSTR(name,',') + 2
             ,LENGTH(TRIM(name))-(INSTR(name,',')+
                decode(length(trim(substr(trim(name),-2)))
                      ,1,3
                      ,0))) first_name
      ,decode(length(trim(substr(name,-2)))
             ,1,SUBSTR(name,-1)) mid_init
  from t1;

FULL_NAME              LAST_NAME  FIRST_NAME MID_INIT
---------------------- ---------- ---------- --------
Rock, F M              Rock       F          M
St. Clair, Mary Ann E  St. Clair  Mary Ann   E
Chung, Wang            Chung      Wang

Note: The above code depends upon your ensuring that all last names end with a comma+space, and middle initials being a single character (without punctuation) at the end of NAME. If you have additional data idiosyncracies that you haven't told us about yet, then please tell us.

Let us know how the above code works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fmrock]

Thanks Santa and smbpalepilsen.

I totally agree this is a design failure. But this is a 3rd party application we use and we have no control over the db... just the abilty to pull reports from it. I am trying to put this in a specfic format for another vendor.

Anyways, all seems to work well, but there are some names in the list that have no comma. These are company names instead of a persons name. These names end up in the First name field.

 

[SantaMufasa]

These are company names instead of a persons name. These names end up in the First name field.

FM, Is this an acceptable result?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”