• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

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

0
Nathan Riley
Asked:
Nathan Riley
1 Solution
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now