none
Change the name format

    Question

  • HI guys,

    I need SQL to convert names from Abeja, Maribell to Maribell Abeja. I tried to split by using ',' but couldn't get it. Please help me. Thanks.


    svk

    Thursday, March 29, 2012 5:07 PM

Answers

  • You can either use charindex or parsename for this; I didn't like the way parsename looked for this so I didn't include it in the example:

    declare @test table (aName varchar(20));
    insert into @test
    select 'Abeja, Maribell' union all
    select 'Madonna' union all
    select null
    ;
    select
      aName,
      case when location = 0 then aName
           else substring(aName, location + 2, len(aName))
              + ' ' + left(aName, location - 1) 
      end as Formatted_Name
    from @test
    cross apply
    ( select charindex(',', aName) as location ) x
    ;
    /* -------- Output: --------
    aName                Formatted_Name
    -------------------- ---------------
    Abeja, Maribell      Maribell Abeja
    Madonna              Madonna
    NULL                 NULL
    */

    Thursday, March 29, 2012 5:09 PM