Here's a different method that's faster. Of course, I can't claim performance improvements unless I can prove it. To do that, we'll setup a 100,000 row test table, like this...

--===== Conditionally drop and repopulate the test table

-- to make reruns in SSMS easier. We're just building

-- test data here. THIS IS NOT A PART OF THE SOLUTION.

IF OBJECT_ID('tempdb..#HOST0140','U') IS NOT NULL

DROP TABLE #HOST0140

;

WITH

cteTally AS

(

SELECT TOP 100000

N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

SELECT NAME = 'LastName'+CAST(N AS VARCHAR(10))

+ ', '

+ 'FirstName'+CAST(N AS VARCHAR(10))

INTO #HOST0140

FROM cteTally

;

Here's an iTVF being used as an iSF (see http://www.sqlservercentral.com/articles/T-SQL/91724/

for more on those)...

CREATE FUNCTION dbo.ReverseName

(@pString VARCHAR(8000),@pDelimiter VARCHAR(5))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH cteFindDelimiter AS

(

SELECT DelimiterPosition = CHARINDEX(@pDelimiter,@pString)

)

SELECT ReversedName = CASE

WHEN DelimiterPosition > 0

THEN LTRIM(SUBSTRING(@pString,DelimiterPosition+LEN(@pDelimiter),8000))

+ ' '

+ SUBSTRING(@pString,1,DelimiterPosition-1)

ELSE @pString

END

FROM cteFindDelimiter

;

Now, a test to compare the two. The @Bitbucket variable takes the display time out of the picture.

--===== Declare a timer variable.

DECLARE @StartTime DATETIME;

--===== Create variable to take display times out of the picture.

DECLARE @Bitbucket VARCHAR(8000);

RAISERROR('========== ReverseName ========================================',0,1)

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = r.ReversedName

FROM #HOST0140 h

CROSS APPLY dbo.ReverseName(h.Name,',') r;

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));

RAISERROR('========== udf_ReverseNames ===================================',0,1)

SELECT @StartTime = GETDATE();

SELECT @Bitbucket = dbo.udf_ReverseNames(h.Name,',')

FROM #HOST0140 h;

PRINT 'Duration (ms): ' + CAST(DATEDIFF(ms,@StartTime,GETDATE()) AS VARCHAR(10));

Here are the results from that test. "ReverseName" is new function with no While Loop.

========== ReverseName ========================================

Duration (ms): 450

========== udf_ReverseNames ===================================

Duration (ms): 2123