SQL Server - Convert LASTNAME, FIRSTNAME to camel case Firstname Lastname

Question

Hi as it states in the title i have names in the format LASTNAME, FIRSTNAME and i wish to have the data in the format Firstname Lastname. This is going to be used in a OLE DB SOURCE query in SSIS so do not want it in a function. How can this be done?

Answer 1

 declare @NameString Varchar(50) = 'BLOGGS, JOE'

   SELECT  CASE WHEN CHARINDEX(',',@NameString) = 0 THEN 'Unexpected format: ' + @NameString ELSE 
  UPPER(LEFT( RIGHT( @NameString,len(@NameString)-CHARINDEX(',',@NameString)-1 ),1)) +   --First Initial
   LOWER(SUBSTRING(@NameString,CHARINDEX(',',@NameString)+3,LEN(@NameString))) -- rest of first name


  +' ' +
  UPPER(LEFT(@NameString,1)) -- Surname initial
   + LOWER(RIGHT(LEFT(@NameString,CHARINDEX(',',@NameString)-1),LEN(LEFT(@NameString,CHARINDEX(',',@NameString)-1))-1)) -- rest of surname
   END