Changing the Order of First Name, Last Name and Middle Initial

Every now and then when we are using names we convert the format based on the application, reporting purposes or as per business request.

Here is a small example of how to change the format of a name field in the order as per requested.

I am using Space as a qualifier in CHARINDEX to get the spaces in between the names to determine the first name, middle initial and last name

DECLARE @subscribername VARCHAR(100)

                ,@firstname VARCHAR(100)

                ,@middleintial VARCHAR(100)

                ,@lastname VARCHAR(100)

SET @subscribername = 'JOHN L DOE'

DECLARE @name VARCHAR(100)

SET @name = @subscribername

SELECT

                @firstname= substring(@name, 1, charindex(' ', @name))

                 ,@middleintial=substring(substring(@name,(len(@name) - charindex(' ', @name) + 1),len(@name)),0,CHARINDEX(' ',substring(@name,(len(@name) - charindex(' ', @name) + 1),len(@name))))

                 ,@lastname=substring(substring(@name,(len(@name) - charindex(' ', @name) + 1),len(@name)),CHARINDEX(' ',substring(@name,(len(@name) - charindex(' ', @name) + 1),len(@name))),len(@name))

               

SELECT @firstname as  FirstName

                                                ,@middleintial as MiddileInitial

                                                ,@lastname as LastName

                                                ,@lastname+' '+@firstname+' , '+@middleintial as FullName