SQL Help

I'm creating user names based on the following:

substring(firstname,1,1)+isnull(substring(middlename,1,1),'')+lastname

So the user names are usually BAMoses or KMGivler or DARexroth which works well.

What I'm running into is last names that are hyphenated like Brabham-Lawrence which then creates the user name IABrabham-Lawrence or the last name will have an apostrophe in it like O'Donnell which then returns the user name of PDO'Donnell.

I need assistance accounting for this by either truncating the name before the hyphen (which I prefer) and removing the apostrophe.

Thanks for any guidance!
jasonbrandt3Asked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:


substring(firstname,1,1)+isnull(substring(middlename,1,1),'') +
REPLACE((case when (CHARINDEX('-', LastName, 1)) > 0 then
           LEFT(LastName, CHARINDEX('-', LastName, 1)-1)
           ELSE LastName END), '''', '')
0
 
Ephraim WangoyaCommented:

try


substring(firstname,1,1)+isnull(substring(middlename,1,1),'')
REPLACE((case when (CHARINDEX('-', LastName, 1)) > 0 then 
           LEFT(LastName, CHARINDEX('-', LastName, 1)-1) 
           ELSE LastNameEND), '''', '')

Open in new window

0
 
Ephraim WangoyaCommented:
i missed the +
substring(firstname,1,1)+isnull(substring(middlename,1,1),'') +
REPLACE((case when (CHARINDEX('-', LastName, 1)) > 0 then 
           LEFT(LastName, CHARINDEX('-', LastName, 1)-1) 
           ELSE LastNameEND), '''', '')

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
CarlsbergFTWCommented:
have you tried using "case" within the section that is generating the last name ?

CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END
0
 
jasonbrandt3Author Commented:
@ewangoya:

That would work for the hyphen, how would I remove an apostrophe if their last name has one like O'Donnell?
0
 
Ephraim WangoyaCommented:

That removes the Hyphen as well
REPLACE checks for hyphen and replaces with blank character
0
 
jasonbrandt3Author Commented:
Perfect!  I just ran it, exactly how I wanted it.  Thanks so much for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.