Why does REPLACE behave differently

[petertickler]

In most of my databases, the command SELECT REPLACE('Freddie Kruger',' ','') returns the line:

FreddieKruger


However in one database (on the same server), this command returns:

Freddie Kruger


Can someone tell me why?

Peter Tickler

 

[jayfox]

I wish I can help you I'm having the same trouble and I can't figure this out

 

[vongrunt]

This character is exactly at 24th position (3x8) ... perhaps you used Tab character in Query Analyzer?

 

[SQLBill]

In the database where it isn't working, check the data to make sure it was entered with only one space. This:

Freddy Kruger

is not the same as:

Freddy Kruger

Also, double check to make sure its spelled correctly..

Freddy Kruger is not the same as Freddy Krugar.

-SQLBill

 

[petertickler]

The example of 'Freddie Kruger' is only an example. I actually want to use the REPLACE with fields so that I can do a string search ignoring all spaces.

My guess is that there is some setting in the databases that is different which affects how this command operates with spaces.

 

[SQLBill]

I was wrong about the amount of spaces that command will remove.

The command is correct and will remove all the spaces. If it's not working somewhere, you need to check the data.

Another possibility is: How are you telling the command which field(s) to check? If it's for more that one database is the field name the same? If you are using REPLACE(MyField, ' ', '') and one database has MyFiled or MyFields (or any other non-match) the script won't work for that database.

-SQLBill

Posting advice: FAQ481-4875