CODEWITHSUNDEEP
sqlsql-serversql-server-2008
chamara
chamara

Reputation: 12709

Split and merge string

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: [email protected] 'Natalie' - First Name 'B' - First letter of the last name

Upvotes: 0

Views: 71

Answers (3)

user3589536
user3589536

Reputation: 204

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? [email protected]?

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'

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6071

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.

Upvotes: 1

mxix
mxix

Reputation: 3659

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

Upvotes: 1

Related Questions