Solved

SQL Help

Posted on 2011-03-14
7
282 Views
Last Modified: 2012-08-14
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!
0
Comment
Question by:jasonbrandt3
  • 4
  • 2
7 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35128748

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
 
LVL 32

Expert Comment

by:ewangoya
ID: 35128758
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
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35128761
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:jasonbrandt3
ID: 35128955
@ewangoya:

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

Expert Comment

by:ewangoya
ID: 35128987

That removes the Hyphen as well
REPLACE checks for hyphen and replaces with blank character
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35128997


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
 

Author Closing Comment

by:jasonbrandt3
ID: 35129023
Perfect!  I just ran it, exactly how I wanted it.  Thanks so much for the help!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now