Separating First Name and Last Name from Name

There are bargains when user want to separate first and second name from Name field generally I have seen this for creating email address for in-house projects.

So if your requirement is to separate name to first name and last name then you can easily implement it with the help of charIndex and substring function of SQL.

Let’s do a practical for the same

create table #demoSeprateName

(name varchar(20),

first_name varchar(10),

last_name varchar(10))

insert into #demoSeprateName(name) values('Neha Sharma')

insert into #demoSeprateName(name) values('Richa Sharma')

select substring(name,1,charindex(' ',name)) as 'First Name',

substring(name,charindex(' ',name) + 1,len(name)) as 'Last Name'

from #demoSeprateName

First Name           Last Name

-------------------- --------------------

Neha                 Sharma

Richa                Sharma

(2 row(s) affected)

So here we have used charIndex function, it will give the position of the specified character, and substring function with which give the subsring of a given string starting from position mention to length specified.  

Let’s update the table with first_name and last_name value

update #demoSeprateName

set first_name = substring(name,1,charindex(' ',name)),

last_name = substring(name,charindex(' ',name) + 1,len(name))

name                 first_name last_name

-------------------- ---------- ----------

Neha Sharma          Neha       Sharma

Richa Sharma         Richa      Sharma

(2 row(s) affected)

Now if you want to generate email address in the format of firstName.LastName@abc.com you can do this

select ltrim(rtrim(substring(name,1,charindex(' ',name))))

+ '.' +

ltrim(rtrim(substring(name,charindex(' ',name) + 1,len(name))))

+ '@xyz.com'

as 'Email Address'

from #demoSeprateName

Email Address

-------------------------------------------------

Neha.Sharma@xyz.com

Richa.Sharma@xyz.com

Be serious about Coding, if you want to have any amusement in coding.

Post Reference: Vikram Aristocratic Elfin Share