Transforming a Name from First-Last to Last-First

The following function will transform a name from first last to last, first form. For example, for "John Fitgerald Kennedy" it returns "Kennedy, John Fitzgerald".

NOTE: This transformation is not appropriate for names with a generational suffix, such as "Jr.", "III", etc. For example, the name "Martin Luther King, Jr." gets transformed to "Jr., Martin Luther King,"

create function [dbo].[TransformName]
    @inputText varchar(max)
    ) returns varchar(max) as begin

     @result    varchar(max)
    ,@pos       int

     @pos    = len(@inputText) - charindex(' ', reverse(@inputText), 1) + 2
    ,@result = substring(@inputText, @pos, 8000) + ', ' + substring(@inputText, 1, @pos-2)

return @result