It's interesting how in this discussion we have all been very busy trying to map numbers to the days of the week to 'make sense out of them' and to...

Don't forget language issues. Where I am, when I do

SELECT...

In many cases, I can do what I have to do by simply capturing the weekday values for a known saturday and sunday, like so:

declare @dowSat int

declare @dowSun int

set @dowSat=datepart(dw,'20061028')

set...

Well, after some hard looking around I found the cause. It appears that some nitwit dba at this site for some reason created a db schema called 'DBO'. While this...

David,

You say everything can be done with machine code.

But does machine code understand the concept of tables?

Or do you insert the concept of tables into your machine code?

And you say:

Data...

Mitesh, it was mentioned earlier that using varchar(max) solves the problem of data loss.

OK. I see it now. Missed out on the 5K+5K.

And the HasSpaces will benefit more any algorithm that does particularly poor with strings having no double spaces.

C#Screw

I decided to look at your benchmarking script and I must say there are many interesting things.

By the way, why does Mitesh have a run count of only 1?

And why...

The problem is replicate which will not return more than 8000 characters.

Thus this works just fine.

declare @STR varchar(max)

declare @s1 varchar(max)

declare @s2 varchar(max)

declare @s3 varchar(max)

set @s1=replicate(' ',4000)

set @s2=replicate(' ',4000)

set...

Very strange this MAX stuff on SS2005. The code below behaves very strange. I.e. I set a string to 8004 characters and yet the LEN function shows it to be...

Interesting idea. And it's collation insensitive too.

But this approach will lose data!

Thus try:

DECLARE @STR VARCHAR(8000)

set @STR='*' + replicate(' ',7998) + '*'

select @STR

select

Jeff Moden (11/22/2009)

Very cool revelation but I'd suggest using the same spacing scheme...

The problem is the where clause in your function.

It gets interpreted only once.

C#Screw

My compliments on this neat cleanup engine. Here are some comments:

In the 32+1, 16+1, etc part of the code, you need to add an extra replace of two spaces with...

Paul,

BOL doesn't explain the guts of what goes on behind collate.

I'm no expert on collate but I have a rough feeling of what it is all about.

This little example makes...