Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Splitting first/last names

Posted on 2011-04-21
16
Medium Priority
?
251 Views
Last Modified: 2012-05-11
I found a problem with the other questions resolution it worked fine when the data was:

John L Smith
John Doe

But now I found that some names are

John L Smith JR
John Doe JR

So it splits them like

firstname = John L Smith and Last name is JR

how can I modify to account for this?
0
Comment
Question by:Nathan Riley
  • 7
  • 5
  • 2
  • +2
16 Comments
 
LVL 1

Expert Comment

by:SudhiDesh
ID: 35441566
hi, how do you want the results displayed for the names?
John L Smith JR
John Doe JR
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35441582
What's the expected last name in those cases?  'Doe JR' or 'Doe'?
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 35441592
I would like it to be

FirstName
John L


LastName
Smith Jr
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Expert Comment

by:AkAlan
ID: 35441612
Not sure what you are needing here but I would have four seperate columns for an individuals name:

LastName
FirstName
MiddleInitial or MiddleName
Suffix

Then in your query you can concatonate them like this

Select LastName + ', ' + FirstName + ' ' + ISNULL(MiddleInitial,'') + ' ' + ISNULL(Suffix,'')

Let me know if that is not what you are asking.
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 35441624
Forgot one thing, you need to alias the Selecxt statement.
Select LastName + ', ' + FirstName + ' ' + ISNULL(MiddleInitial,'') + ' ' + ISNULL(Suffix,'') as PersonName

 
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 35441625
@AkAlan: No I'm splitting them out all from one field
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 35441631
Here is the code from the related question that worked on splitting them without the JR on there

select LEFT(RSPName, DATALENGTH(RSPName) - CHARINDEX(' ', REVERSE(RSPName), 1)) First,
       REVERSE(LEFT(REVERSE(RSPName), CHARINDEX(' ', REVERSE(RSPName), 1))) Last
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35442155
I recommend that you create the two functions below (FirstName, LastName) and use it in your queries.  If you run into other suffixes just include them on lines #11 and #27 below.

Create Function FirstName
(
	@name varchar(50)
)
RETURNS varchar(50)
AS
BEGIN

SET @name = REPLACE(@name, ' ', '.')

RETURN Case When parsename(@name, 1) in ('JR')
            Then ISNULL(parsename(@name, 4) + ' ', '') + parsename(@name, 3)
            Else ISNULL(parsename(@name, 4) + ' ', '') + ISNULL(parsename(@name, 3) + ' ', '') + parsename(@name, 2) End

END
GO    
Create Function LastName
(
	@name varchar(50)
)
RETURNS varchar(50)
AS
BEGIN

SET @name = REPLACE(@name, ' ', '.')

RETURN Case When parsename(@name, 1) in ('JR')
            Then parsename(@name, 2) + ' ' + parsename(@name, 1)
            Else parsename(@name, 1) End

END
GO

Open in new window


You can use them like this:
select dbo.FirstName(YourNameColumn) FirstName, 
       dbo.LastName(YourNameColumn) LastName
  from YourTable

Open in new window

0
 
LVL 2

Expert Comment

by:roshnipatel
ID: 35442699
You may also want to add addtional suffixes in the IN clause: ie. Sr., Sr, Jr., III
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 35443315
It isn't working your functions are returning NULLS, but their is data there.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35443366
Please provide some sample names that are returning NULLS.
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 35443376
JOHN E. TESTINGS JR
TESTING C. TEST JR
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 35443586
I corrected the issue.  Please try the following:

Create Function FirstName
(
	@name varchar(50)
)
RETURNS varchar(50)
AS
BEGIN

SET @name = REPLACE(REPLACE(REPLACE(@name, '  ', ' '), '.', '~'), ' ', '.')

SET @name = Case When parsename(@name, 1) in ('JR')
            Then ISNULL(parsename(@name, 4) + ' ', '') + parsename(@name, 3)
            Else ISNULL(parsename(@name, 4) + ' ', '') + ISNULL(parsename(@name, 3) + ' ', '') + parsename(@name, 2) End

RETURN REPLACE(@name, '~', '.')
END
GO    
Create Function LastName
(
	@name varchar(50)
)
RETURNS varchar(50)
AS
BEGIN

SET @name = REPLACE(REPLACE(REPLACE(@name, '  ', ' '),'.','~'), ' ', '.')

SET @name = Case When parsename(@name, 1) in ('JR')
            Then parsename(@name, 2) + ' ' + parsename(@name, 1)
            Else parsename(@name, 1) End

RETURN REPLACE(@name,'~','.')
END
GO

Open in new window

0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 35443758
Ok, that's very close now.  There is still a few nulls though, when their middle name is spelled out like

John Testing Smith

Can you add that in there as well, then it will be good.

Thanks!
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35443902
Humm... that's working on my end.  The problematic name should be something else.

This works:

select dbo.LastName('John Testing Smith'), dbo.Firstname('John Testing Smith')

Output

LastName FirstName
------------ ---------------
Smith        John Testing
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 35444107
Actually I did find a few others that don't work, I created a new question for them here: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26971067.html
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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