All Forums Database
Niesh20us 78 posts Joined 06/13
17 Jul 2013
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

Niesh20us 78 posts Joined 06/13
17 Jul 2013

Help please

Glass 225 posts Joined 04/10
17 Jul 2013

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

VandeBergB 182 posts Joined 09/06
17 Jul 2013

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...

Some drink from the fountain of knowledge, others just gargle.

Kbos 20 posts Joined 04/13
17 Jul 2013

You may check the following link
 
http://forums.teradata.com/forum/enterprise/split-delimited-column-into-separate-rows

vikas_yadav 19 posts Joined 09/12
17 Jul 2013

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

 

 

2aravinth 3 posts Joined 07/13
18 Jul 2013

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
 

-richardM 1 post Joined 07/14
18 Jul 2014

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

You must sign in to leave a comment.