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.
| 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.0this 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 |
 |
|
|
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 |
|
|
|
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! |
|
|
|
|
|
|
|
|
REPLACE command in sql server