Jeff,

When you deal with two different but similar problems, things tend to get confusing.

Problem 1: SQL Server 2000 and the replace function's reliability

Problem 2: SQL Server 2005 and Paul's performance...

Jeff,

By the way, once you have attached the collate qualifier to OriginalString the others are not necessary.

In any case, in my opinion this is a bug in SQL Server 2000...

Jeff Moden (11/21/2009)

---

Michael Meierruth (11/21/2009)

---

In SQL Server 2000 it's a real nightmare. Performance is fairly stable but for many situations the calls to replace actually don't work, in particular when...

Paul White (11/20/2009)

---

My thanks to those who have voted for the Connect item about the collation performance thing. I'll post the response here as soon as I hear anything...

Are on SQL Server 2000? If so, Jeff's code should fail with this. Please try Jeff's...

Finally found a solution to this collate problem.

In Jeff's code if you change the line

LTRIM(RTRIM(OriginalString))

to

LTRIM(RTRIM(OriginalString collate your_database_collation_name))

the code starts to work. You must specify the collation...

Regarding collation, what I seem to be discovering is that any SQL Server 2000 database whose collation is different from the instance default will cause Jeff's code NOT to work.

I...

C#Screw

There are more or less 17 different means of 'Nube', one of which is 'new'. So this is what I'm assuming. You might want to look up the others -...

IMO is short for In My Opinion.

C#Screw,

I use your method. But knowing that this not always reliable, I generally run a test several times until it 'settles down' to a pretty constant value. IMO it's the...

Regarding this issue of collate, I have also run into a situation where, based on the database I'm in, Jeff's sample code from the article simply doesn't work, i.e. the...

How did you make this calculation if...

I noticed that 'vliet' in a post earlier today seems to know about my binary approach calling it the "65, 33, 17, 9, 5, 3 an 2 spaces nested REPLACE...

Hello C# Screw,

I'm surprised to see me below a CLR implementation.

You might want to put in this line which uses hard coded string constants instead of the replicate function.

I might...

Earlier on in this post I mentioned the use of a binary approach. I finally found some time to try this out.

declare @s-2 varchar(8000)

set @s-2 =...