How to replace a comma with a space in a field

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to replace a comma with a space in a field

Author

Topic

jej1216
Starting Member

27 Posts

Posted - 2007-04-05 : 18:28:32

We have a field called NAME and the data in it is like this:
'Smith, Joe'
We need to replace the comma with a space so it looks like this:
'Smith Joe'
I know in Oracle I could use TRANSLATE to change the comma to a space. Is there an equivalent to TRANSLATE in SQL Server 2000?

TIA,

jej1216

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-04-05 : 18:32:53

use the Replace function

Replace(name, "," , " ")

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-05 : 18:34:35

Instead of using double quotes, use single-quotes:

UPDATE YourTable
SET Name = REPLACE(Name, ',', ' ')

SQL Server Helper
http://www.sql-server-helper.com

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-04-06 : 00:08:14

whoaa, correct. good call sshelper. i musta been sleepwalking lol

jej1216
Starting Member

27 Posts

Posted - 2007-04-06 : 10:07:54

Great - thanks.

REPLACE is more intuitive than TRANSLATE anyway!

jej1216

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 03:23:55

quote:
Originally posted by russell

use the Replace function

Replace(name, "," , " ")

I think you have given ACCESS syntax

Madhivanan

Failing to plan is Planning to fail

Johnnymarvellous
Starting Member

10 Posts

Posted - 2007-06-22 : 10:41:06

Hello,

I used the REPLACE example given above for a similar problem and thank you, I now have no commas in the text, however I now have a new problem.....
The column in question would apear in the extract file with a lenght of 50 charaters, however, since using the REPLACE command, the column is now 500+ characters in length (which is making a mess for the downstream system)

If I then try to wrap the replace section to try and CAST, CONVERT or RTRIM, it simply returns an error.

Can anyone tell me:
a. why has the column grown to half a mile in length
b. how can I stop this/shorten the output for this column.

You can probably tell, I'm not that experienced with SQL, thanks.
JM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-22 : 11:24:24

Can you post some sample data and the query you used?
How can replacing comma result in increasing the length?
Also post this as new topic to get more replies

Madhivanan

Failing to plan is Planning to fail

Johnnymarvellous
Starting Member

10 Posts

Posted - 2007-06-22 : 12:07:09

quote:
Originally posted by madhivanan

Can you post some sample data and the query you used?
How can replacing comma result in increasing the length?
Also post this as new topic to get more replies

Madhivanan

Failing to plan is Planning to fail

OK, will do thanks
JM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources