Splitting first/last names

hi, how do you want the results displayed for the names?
John L Smith JR
John Doe JR

What's the expected last name in those cases?  'Doe JR' or 'Doe'?

I would like it to be

FirstName
John L


LastName
Smith Jr

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.

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

 

@AkAlan: No I'm splitting them out all from one field

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

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

Select allOpen in new window

You can use them like this:

select dbo.FirstName(YourNameColumn) FirstName, 
       dbo.LastName(YourNameColumn) LastName
  from YourTable

Select allOpen in new window

You may also want to add addtional suffixes in the IN clause: ie. Sr., Sr, Jr., III

It isn't working your functions are returning NULLS, but their is data there.

Please provide some sample names that are returning NULLS.

JOHN E. TESTINGS JR
TESTING C. TEST JR

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!

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

Actually I did find a few others that don't work, I created a new question for them here: https://www.experts-exchange.com/questions/26971067/Trapping-for-more-variables-on-Splitting-out-name-filed.html