Split and merge string

Question

I'm trying to generate an email from full name. Following is what I have and I'm stuck here

select distinct tbin.Name
,(SELECT SUBSTRING(tbin.Name, 1, CHARINDEX(' ', tbin.Name) - 1) AS [FirstName])
,(select SUBSTRING(tbin.Name, CHARINDEX(' ', tbin.Name) + 1, 8000) AS LastName)
from tblInitialApplicants tbin

Name is like 'Natalie Marine Baily'. Name can be of any length and also can have any number of space between each part of the name.

Expected result: Natalie.B@gmail.com 'Natalie' - First Name 'B' - First letter of the last name

score 1 · Accepted Answer · answered Nov 03 '14 at 11:05

You can get the position of a char with CHARINDEX, look for the first space and reverse de name and look for the last. With that you can easly substring first and last name.

DECLARE @Fullname VARCHAR(100) = 'Natalie Marine Baily';

select
    *,
    firstname+'.'+lastnameINITIAL+'@gamil.com'
    from (
select 
    @Fullname Fullname,
    SUBSTRING(@Fullname,1,CHARINDEX(' ',@Fullname,1)) firstname,
    SUBSTRING(SUBSTRING(@Fullname,LEN(@Fullname)-CHARINDEX(' ',REVERSE(@Fullname),1)+2,CHARINDEX(' ',REVERSE(@Fullname),1)),1,1) lastnameINITIAL
    ) T

score 1 · Answer 2 · answered Nov 03 '14 at 11:07

1

DECLARE @name VARCHAR(200) = 'Natalie Marine Baily'

SELECT  LEFT(@name,CHARINDEX(' ',@name,1)-1) + '.'+
        LEFT(REVERSE(LEFT(REVERSE(@name),CHARINDEX(' ',REVERSE(@name),1)-1)),1) + '@gmail.com'

Result:

enter image description here

Just see, whether you can work from here.

answered Nov 03 '14 at 11:07

Jithin Shaji

5,893
5
25
47

score 1 · Answer 3 · edited May 23 '17 at 11:49

Your biggest problem is that both first names and last names can have spaces between them. So how will you know where the last name begins? For example Jean-Claude Van Damme.

Also what should the email look like in this case? jean-claude.v@gmail.com?

I would suggests you change your database to store the first and last names as separate fields, or better yet have an email field. Then this will be much easier. If you cannot do that you will have to generate a few possible emails and validate if the emails exist.

How to check if an email address exists without sending an email?

This would be how to generate an email if you take it that the first word is the firstname and the last word is the lastname.

DECLARE @Name varchar(100) = 'Natalie Marine Baily'

select SUBSTRING(@Name, 0, CHARINDEX(' ', @Name) - 1) 
+ '.' + SUBSTRING(@Name,LEN( @Name) - CHARINDEX(' ',REVERSE(@Name))+2 , 1) + '@gmail.com'

Split and merge string

3 Answers3