We help IT Professionals succeed at work.

Trapping for more variables on Splitting out name filed

171 Views
Last Modified: 2012-06-27
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

Comment
Watch Question

Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.