Trapping for more variables on Splitting out name filed

Currently using the below code, but found some places it's not working.  Examples that it returns nulls are:

John Smith and ASSC
John J. De La Smith
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

LVL 12
Nathan RileyFounderAsked:
Who is Participating?
 
wdosanjosCommented:
Here is the updated script addressing the 'John J. De La Smith' issue.  Regarding the other issue ('John Smith and ASSC'), what's the expected result (First Name / Last Name), currently it returns ASSC (Last Name) and 'John Smith and' (First Name)

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

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

WHILE (@name IS NOT NULL AND parsename(@name,1) IS NULL) 
	SET @name = STUFF(@name, CHARINDEX('.', @name), 1, ' ')

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, '  ', ' '),'.','~'), ' ', '.')

WHILE (@name IS NOT NULL AND parsename(@name,1) IS NULL) 
	SET @name = STUFF(@name, CHARINDEX('.', @name), 1, ' ')

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
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.