none
Within SSMS calculated column changes format of name

    Question

  • I have a column "Name" which is the format John Doe .

    How would I make my new computed column take this name and make it "Doe, John"?

    I originally had it splitting the column into First and Last, then combining them. But there has to be a way to do it within one column instead of using 3. right?

    Thanks,


    • Edited by DCDeez Wednesday, August 21, 2013 5:45 PM
    Wednesday, August 21, 2013 5:44 PM

Answers

  • Sarat,

    Your column gives me what I want except it doesn't have a space after the "," how would I add that?

    try this,

    drop table #test
    create table #test(username varchar(100), 
    computed_username as STUFF(username,1,charindex(' ',username),'')+','+space(2)+ STUFF(username,charindex(' ',username),len(username),''))
    
    insert into #test(username) values('John Doe')
    
    select *
    from #test

    Adding SPACE(n) function will create empty spaces. 

    Does all the names have only two parts, whatever i gave was a sample. Based on your input data you need to modify the logic.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 22, 2013 12:40 AM
  • Sorry I didn't mean to say it didn't work. It just works incorrectly.

    (((stuff([Contract_Specialist],(1),charindex(' ',[Contract_Specialist]),'')+',')+space((2)))+stuff([Contract_Specialist],charindex(' ',[Contract_Specialist]),len([Contract_Specialist]),''))

    Creates a name with an extra space in between.

    John Doe becomes "Doe,  John"

    By the way, thank you so much for the continual help. I know I haven't been responding in the timeliness manner.

    may be I added an additional space to show difference.

    In my query I used SPACE(2), so it gives 2 spaces gap, replace it with SPACE(1) , that might give you desired result.

    Have a look at this for more understanding on SPACE function http://technet.microsoft.com/en-us/library/ms187950.aspx.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by DCDeez Thursday, August 29, 2013 5:28 PM
    Thursday, August 29, 2013 5:19 PM

All replies

  • Hope that would help:

    Create Function GetFormattedName(@Name Varchar(100))
    Returns varchar(100)
    As
    Begin
    Declare @LastSeparatorIndex tinyint=CHARINDEX(' ',@name)-1

    Return RIGHT(@Name,@LastSeparatorIndex)+','+Left(@Name,Len(@Name)-@LastSeparatorIndex)
    End


    Select dbo.GetFormattedName('John Malcom Doe')

    Wednesday, August 21, 2013 6:03 PM
  • Please try the following:

    create function reverseName(@name varchar(25))
    	returns varchar(25)
    as
    begin
    	if charindex(' ', @name, 1) > 0
    	begin
    		select @name = isnull(right(@name, len(@name) - charindex(' ', @name, 1)) + ', ' + left(@name, charindex(' ', @name, 1)-1), @name)
    	end
    
    	return @name
    end
    go
    
    create table test
    (
    	name varchar(25),
    	name2 as dbo.reverseName(name)
    )
    
    insert into test
    select 'John Doe'
    union
    select 'Madonna'
    union
    select 'John Smith'
    
    select *
    from test

    Wednesday, August 21, 2013 6:07 PM
  • something like this?

    create table #test(username varchar(100), 
    computed_username as STUFF(username,1,charindex(' ',username),'')+','+ STUFF(username,charindex(' ',username),len(username),''))
    
    insert into #test(username) values('John Doe')
    
    select *
    from #test


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 21, 2013 6:13 PM
  • Hope that would help:

    Create Function GetFormattedName(@Name Varchar(100))
    Returns varchar(100)
    As
    Begin
    Declare @LastSeparatorIndex tinyint=CHARINDEX(' ',@name)-1

    Return RIGHT(@Name,@LastSeparatorIndex)+','+Left(@Name,Len(@Name)-@LastSeparatorIndex)
    End


    Select dbo.GetFormattedName('John Malcom Doe')

    I would go for Kevin's solution. However fixing my bug:

    Create Function GetFormattedName(@Name Varchar(100))
    Returns varchar(100)
    As
    Begin
    Declare @LastSeparatorIndex tinyint=CHARINDEX(' ',Reverse(@name))

    Return Case When @LastSeparatorIndex>0 Then RIGHT(@Name,@LastSeparatorIndex-1)+','+Left(@Name,Len(@Name)-@LastSeparatorIndex)
                                           Else  @Name End
    End

    Wednesday, August 21, 2013 6:22 PM
  • Sarat,

    Your column gives me what I want except it doesn't have a space after the "," how would I add that?

    Wednesday, August 21, 2013 6:25 PM
  • Sarat,

    Your column gives me what I want except it doesn't have a space after the "," how would I add that?

    try this,

    drop table #test
    create table #test(username varchar(100), 
    computed_username as STUFF(username,1,charindex(' ',username),'')+','+space(2)+ STUFF(username,charindex(' ',username),len(username),''))
    
    insert into #test(username) values('John Doe')
    
    select *
    from #test

    Adding SPACE(n) function will create empty spaces. 

    Does all the names have only two parts, whatever i gave was a sample. Based on your input data you need to modify the logic.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 22, 2013 12:40 AM
  • This is exactly what I need, But I can't figure out what to put in the "Computed Column" formula section in SSMS.

    Would it just be..."STUFF(username,1,charindex(' ',username),'')+','+space(2)+ STUFF(username,charindex(' ',username),len(username),''))"  ?

    This doesn't seem to work.

    Monday, August 26, 2013 1:51 PM
  • This is exactly what I need, But I can't figure out what to put in the "Computed Column" formula section in SSMS.

    Would it just be..."STUFF(username,1,charindex(' ',username),'')+','+space(2)+ STUFF(username,charindex(' ',username),len(username),''))"  ?

    This doesn't seem to work.


    Hi DCDeez,

    Yes, I found you paste more ")" with you codes, please try the following codes:

    STUFF(username,1,charindex(' ',username),'')+','+space(2)+ STUFF(username,charindex(' ',username),len(username),'')


    Allen Li
    TechNet Community Support


    Tuesday, August 27, 2013 6:49 AM
  • Still doesn't work in the SSMS Computed column.

    STUFF(username,1,charindex(' ',username),'')+','+space(2)+ STUFF(username,charindex(' ',username),len(username),'')

    Will not work if dropped into a Computed column. Within a SQL query it works fine.

    Thursday, August 29, 2013 3:49 PM
  • Did you try the sample query I gave?

    create table #test(username varchar(100), 
    computed_username as STUFF(username,1,charindex(' ',username),'')+','+space(2)+ STUFF(username,charindex(' ',username),len(username),''))
    
    insert into #test(username) values('John Doe')
    
    select *
    from #test

    Give us the query which is not working , we may be able to guide if anything wrong with the query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 29, 2013 4:47 PM
  • Sorry I didn't mean to say it didn't work. It just works incorrectly.

    (((stuff([Contract_Specialist],(1),charindex(' ',[Contract_Specialist]),'')+',')+space((2)))+stuff([Contract_Specialist],charindex(' ',[Contract_Specialist]),len([Contract_Specialist]),''))

    Creates a name with an extra space in between.

    John Doe becomes "Doe,  John"

    By the way, thank you so much for the continual help. I know I haven't been responding in the timeliness manner.

    Thursday, August 29, 2013 5:00 PM
  • Sorry I didn't mean to say it didn't work. It just works incorrectly.

    (((stuff([Contract_Specialist],(1),charindex(' ',[Contract_Specialist]),'')+',')+space((2)))+stuff([Contract_Specialist],charindex(' ',[Contract_Specialist]),len([Contract_Specialist]),''))

    Creates a name with an extra space in between.

    John Doe becomes "Doe,  John"

    By the way, thank you so much for the continual help. I know I haven't been responding in the timeliness manner.

    may be I added an additional space to show difference.

    In my query I used SPACE(2), so it gives 2 spaces gap, replace it with SPACE(1) , that might give you desired result.

    Have a look at this for more understanding on SPACE function http://technet.microsoft.com/en-us/library/ms187950.aspx.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by DCDeez Thursday, August 29, 2013 5:28 PM
    Thursday, August 29, 2013 5:19 PM
  • Perfect. I hope someone is paying you a lot of money for your genius!
    Thursday, August 29, 2013 5:28 PM