Post reply

Swap firstname to lastname

  • February 23, 2015 at 9:12 am

    #295442

    I have column called fullname which has firstname lstname example John Smith

    How can I get it to Lastname, Firstname like Smith , John

  • February 23, 2015 at 9:14 am

    #1779305

    This link helped

    https://social.msdn.microsoft.com/forums/sqlserver/en-US/bfd922e5-8bf7-42ab-becb-d3fc4faf44b5/lastname-firstname-switch

  • February 23, 2015 at 9:23 am

    #1779310

    Any other ideas?

  • February 23, 2015 at 10:21 am

    #1779331

    This would work provided there are always 2 and only 2 names separated by a single space:

    select

    substring(fullname,charindex(' ',fullname) + 1,len(fullname) - charindex(' ',fullname)) + ', ' + left(fullname,charindex(' ',fullname)-1)

    from YourTableName

  • February 23, 2015 at 10:44 am

    #1779338

    Slightly shorter version with the same problem as the one from Adam.

    WITH SampleData(fullname) AS(

    SELECT 'Peter Parker' UNION ALL

    SELECT 'Harry Osborn' UNION ALL

    SELECT 'Mary Jane Watson' UNION ALL

    SELECT 'John Jonah Jameson Jr.'

    )

    SELECT fullname,

    STUFF( fullname, 1, charindex(' ',fullname), '') + ', ' + left(fullname,charindex(' ',fullname)-1)

    FROM SampleData

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply