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
 Site Related Forums
 Article Discussion
 REPLACE command in sql server

Author  Topic 

xichlo
Starting Member

2 Posts

Posted - 2002-04-03 : 18:50:04
Hi,

I have a problem with using the replace command when helping my client removing the spaces in a sql table column. my client just updated there database from 6.5 to 7.0
this is what i have on the querry analyzer:

UPDATE membership SET membername=REPLACE(membername,' ','')

it said x number record(s) affected, but the values remain unchanged.
the value still looked like this 'JONH DOE'

I tried:
UPDATE membership SET membername=REPLACE(membername,' ','-')
and it did update the values ('JONH-DOE'). But then if i try:
UPDATE membership SET membername=REPLACE(membername,'-',''),
the spaces are put back into the value ?

Is there any db options I need to know?

Please help!

million thanks,


XichLo

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-03 : 19:12:52
Use the "sp_dbcmptlevel" system procedure to check if your database is in 6.5 compatibility mode. If it is, then SQL Server will interpret an empty string ('') as a single space. You will need to set the compatibility mode to 7.0 in order to have it properly interpret an empty string.

Take some extra consideration before you change the compatibility level, it will affect just about everything in the database, and stored procedures and other queries my no longer function properly. Books Online has details about the differences between the compatibility levels.

Edited by - robvolk on 04/03/2002 19:16:09
Go to Top of Page

xichlo
Starting Member

2 Posts

Posted - 2002-04-05 : 11:03:29
Thank you RobVolk for this helpful information. I think I can set the compatibility level back to 65 when I'm done with my data clean up.

XichLo
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-05 : 11:54:45
NO! That is NOT a good move. If you go up to 7.0 level, STAY THERE!

Go to Top of Page
   

- Advertisement -