declare
	@Input varchar(200)
	,@Delimiter varchar(5)
	,@Output varchar(150)

set @Input = 'Doe, John'
set @Delimiter = ','

WHILE LEN(@Input) > 0
BEGIN
IF CHARINDEX(@Delimiter, @Input) > 0
BEGIN
SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'')
SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))
END
ELSE
BEGIN
SET @Output = @Input + ' ' + ISNULL(@Output,'')
SET @Input = ''
END
END

print SUBSTRING(@Output,0,LEN(@Output))

create function [dbo].[udf_ReverseNames](@Input varchar(20),@Delimiter varchar(5))
returns varchar(100)

as

begin
	declare
		@Output varchar(150)

	WHILE LEN(@Input) > 0
	BEGIN
	IF CHARINDEX(@Delimiter, @Input) > 0
	BEGIN
	SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'')
	SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))
	END
	ELSE
	BEGIN
	SET @Output = @Input + ' ' + ISNULL(@Output,'')
	SET @Input = ''
	END
	END

	return SUBSTRING(@Output,0,LEN(@Output))
end

--===== 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
;

 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
;

--===== 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));

========== ReverseName ========================================
Duration (ms): 450
========== udf_ReverseNames ===================================
Duration (ms): 2123

HOST0140.NAME

NAME=RIGHT(HOST0140.NAME, LEN(HOST0140.NAME)-(1+CHARINDEX(', ', HOST0140.NAME))) + ' ' +
	LEFT(HOST0140.NAME, CHARINDEX(', ', HOST0140.NAME) - 1)