How to change name format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to change name format

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2010-03-03 : 19:34:24

Hello everyone,

Anybody know how to convert 'John Smith' into 'Smith, John' using SQL? Please note that 'John Smith' is a single string stored in a field called NAME. If the first and last name were in separate fields then this would be an easy one. I know it's gonna have to be a combination of nested functions but I'm feeling a little rusty and could use some help. I am doing a simple SELECT.

Thank you so much in advanced!!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 19:57:30

SELECT REPLACE(NAME, ' ', ', ') FROM table_name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 02:07:19

select substring(name,charindex(' ',name)+1,len(name))+', '+left(name,charindex(' ',@s)-1) from your_table

Madhivanan

Failing to plan is Planning to fail

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2010-03-04 : 09:35:36

I used the solution provided by madhivanan. I declared @s as CHAR(40) but all the records extracted are NULL.

Any suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:38:47

quote:
Originally posted by sqlslick

I used the solution provided by madhivanan. I declared @s as CHAR(40) but all the records extracted are NULL.

Any suggestions?

Post the actual cod you used

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:39:35

The code should be

select substring(name,charindex(' ',name)+1,len(name))+', '+left(name,charindex(' ',name)-1) from your_table

Madhivanan

Failing to plan is Planning to fail

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2010-03-04 : 12:42:31

Great, thanks Madhivanan!! You confused me when you used the variable @s in your original response. That worked perfectly!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-05 : 01:42:24

quote:
Originally posted by sqlslick

Great, thanks Madhivanan!! You confused me when you used the variable @s in your original response. That worked perfectly!!

You are welcome

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-13 : 01:43:01

Oh I did not get OP request!
So, another way:

SELECT PARSENAME(name, 1) + ', ' + PARSENAME(name, 2) AS newName
FROM (SELECT REPLACE(name, ' ', '.') 
      FROM table_name
     )d(name);

UenTsin
Starting Member

4 Posts

Posted - 2010-05-13 : 02:44:54

This will not work if the NAME like 'john R smith'...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources