Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Help

Posted on 2011-03-14
7
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:Ephraim Wangoya
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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:Ephraim Wangoya
ID: 35128987

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

Accepted Solution

by:
Ephraim Wangoya earned 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

705 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