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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 REPLACE command in sql server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xichlo
Starting Member

Vietnam
2 Posts

Posted - 04/03/2002 :  18:50:04  Show Profile  Reply with Quote
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

USA
15732 Posts

Posted - 04/03/2002 :  19:12:52  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Vietnam
2 Posts

Posted - 04/05/2002 :  11:03:29  Show Profile  Reply with Quote
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

USA
15732 Posts

Posted - 04/05/2002 :  11:54:45  Show Profile  Visit robvolk's Homepage  Reply with Quote
NO! That is NOT a good move. If you go up to 7.0 level, STAY THERE!

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000