I want to separate the full name in two parts

Database
Enthusiast

I want to separate the full name in two parts

Hi Experts,

I have to divide the full name in two parts and insert them into two diffrent columns and starting char of First name and last name should be on uppar case and rest of them should be in lower case , please provide me sql query for this problem

Example :- NILESH BHAWSAR

First_Name                     Second_Name

Nilesh                             Bhawsar

Thanks,

Nilesh

7 REPLIES
Enthusiast

Re: I want to separate the full name in two parts

Help please

Enthusiast

Re: I want to separate the full name in two parts

SEL UPPER(SUBSTR('NILESH BHAWSAR',1,1))||LOWER(SUBSTR('NILESH BHAWSAR',2,POSITION(' ' IN 'NILESH BHAWSAR') -1)) AS First_Name,

UPPER(SUBSTR('NILESH BHAWSAR',POSITION(' ' IN 'NILESH BHAWSAR') + 1 ,1))||LOWER(SUBSTR('NILESH BHAWSAR',POSITION(' ' IN 'NILESH BHAWSAR') +2)) AS Last_Name

Enthusiast

Re: I want to separate the full name in two parts

New Example:   Elvis Vande Berg....

This problem is simple at first glance, but you need to check for the number of spaces etc.. to effectively pull this off otherwise you contact your "customer" as Elvis Vande or Elvis Berg...

Enthusiast

Re: I want to separate the full name in two parts

Enthusiast

Re: I want to separate the full name in two parts

Hi,

you can use INITCAP function if your on TD 14 else try to install oracle UDF from developer exhange. 

eg:

SEL INITCAP('nILESH');

SEL INITCAP('bHAWSAR');

op:


INITCAP('bHAWSAR')

Bhawsar

Re: I want to separate the full name in two parts

select name,firstname,lastname from (

select position(' ' in name)-1 pos, name,

length(name)-pos rem,

case when pos>0 then

substring(name,1,pos)

else name end as firstname,

case when pos>0 then

substring(name,pos+1,rem)

else null end as lastname

from name_table) x

 My coulmn "name" contains value like

Scenario 1:

"Jeeva Sussendran". My query will split it like "Jeeva" as FIRST NAME & "Suseendran" as LASTNAME.

 Scenario 2:

"George". Then it will be splited as "Manikandan" as FIRST NAME & will populate NULL for the LAST NAME.

If u need any further more clarifications revert me back.

Regards,

Manikandan A

Re: I want to separate the full name in two parts

Hi

With the query you wrote directly above, can you modify it so after the firstname, it finds the middle inital (by selecting the 1 character) and if theres more than one lastname it puts both names into the lastname column?

the query you provided above works great (thank you) but i have customer names that have two lastnames and a middle initial (and sometimes a suffix)

Thanks