Split Name function Performance Improvement

I was able to solve this by using SubString and PatIndex. Thanks everyone.

Solution:

UPDATE TableX
   SET    
      firstname = Substring(fullname, 1, Charindex(' ', fullname) - 1),
      lastname = Substring(fullname, ( Len(fullname) - Patindex(
      '%[ ' + CHAR(8) + ']%',Reverse(fullname)) + 1 ) +1,Len(fullname) - 
      ( Len(fullname) -     Patinde开发者_开发百科x('%[ ' + CHAR(8) + ']%',Reverse(fullname))+ 1 ))
WHERE  ID = @ID
      AND fullname IS NOT NULL
      AND firstname = ''    

Original Question:

My Split Name Function is able to split First and Last Name.

The problem is that I need to run the same function twice in order to get:

• First Name
• Second pass to get the Last Name.

Does anyone have any ideas how to accomplish the same end result with 1 pass?

UPDATE TableX
SET FirstName = dbo.ufn_SplitName(FullName,'fs'),
Lastname = dbo.ufn_SplitName(FullName,'ln')
WHERE Id = @ID AND FullName IS NOT NULL AND FirstName = ''



CREATE FUNCTION [dbo].[ufn_SplitName] 
( 
    @pInput VARCHAR(150), 
    @TypeOfSplit VARCHAR(2)
)

RETURNS NVARCHAR(MAX)
AS
BEGIN 

DECLARE @FINALOUTPUT AS VARCHAR(150)
DECLARE @FirstSpace as int


-- Determine the Number of spaces
SET @FirstSpace = CHARINDEX(' ',@pInput)

-- Get FirstName
if @TypeOfSplit = 'fs'
SET @FINALOUTPUT = LEFT(@pInput,@FirstSpace) 
-- Last Name Does not exist so first name is the only value
if @TypeOfSplit = 'fs' AND @FirstSpace = 0
SET @FINALOUTPUT = @pInput

-- Get last Name
if @TypeOfSplit = 'ln' AND @FirstSpace > 0
SET @FINALOUTPUT = RIGHT(@pInput, CHARINDEX(' ', REVERSE(@pInput)) - 1)
-- Last Name does not exist
if @TypeOfSplit = 'ln' AND @FirstSpace = 0
SET @FINALOUTPUT = ''

return @FINALOUTPUT

Without getting into the specifics of your code, you could change it to be a table-valued function that returns a single row with a column for each of first name and last name. You could then JOIN to the table valued function within your query and use those columns in your query.

I would go grab the split function from here: http://www.sqlservercentral.com/Forums/FindPost944589.aspx