Paul White (11/18/2009)

---

... my (still slightly amateur) C# as usual!

jcrawf02 (11/18/2009)

---

Thought I'd tell you, you guys are just fun to watch.

C# Screw (11/18/2009)

---

Jeff,
as promised : all tests combined SQL & CLR

Beginning execution loop
Batch execution completed 10000 times.
SQL:Looping
2536
SQL: using ||*9*9||
6310
SQL: Jeffs single char
2916
CLR: looping
453
CLR: Not looping using ||*9*9|| technique in C#
296
CLR: Not looping using Jeff's single char technique in C#
416 <-- consistently c# performs slower single space bell character, surprising?
Correction : Noticed one space missing in c#
CLR: Not looping using Jeff's single char technique in C#
246

I am really sorry the c# input has caused some upset, I thought it was really useful/interesting.. so 10/10 article & productive discussion.

GSquared (11/18/2009)

---

I actually discovered this method almost 20 years ago, when I was building WordPerfect macros to clean up documents that had been typed up in an ASCII text editor. Used it to clean up spaces, underlines, even page-lines created by entering arbitrary numbers of hyphens. Came in very, very handy.

Michael Meierruth (11/18/2009)

---

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

declare @s varchar(8000)
set @s = '*' + replicate(' ',7998) + '*'
select len(@s)
set @s = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
  replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')
select len(@s)
select @s

Needless to say I have tried this with Jeff's data as well as with some random data and it works just fine.
As for performance, I generated 100000 records and Jeff's code did in 110 seconds whereas my code did in 16 seconds.
I shaved off a second by replacing the calls to replicate (which I used for clarity) with hardcoded constants or variables.
Oh yes, Jeff's code ran at 100% CPU whereas my code never reached 100%.

There is a recursive effect going on which I'm still trying to understand from a methematical point of view, i.e. for a given N+1 spaces what is the maximum length of blanks that can be reduce to 1 blank using the above approach.

Of course, if someone can find a string with blanks where this doesn't work I will quietly shut up.

TheSQLGuru (11/18/2009)

---

I'll be damned - someone else who did macro-based document management using WordPerfect macros! How kewl is that!? I had 2.5M of macros that did a full-fledged work package management system that included change bar and version tracking, etc. Tied into a dbase IV database system too!

Paul White (11/18/2009)

---

- no doubt by then someone will have found an improvement which runs in negative time Paul

vliet (11/18/2009)

---

Hello Jeff,

Thank you so much for posting this solution! We will apply your method in a SSIS Derived Column Transformation on data coming from raw files. This data needs to be cleansed before it even reaches the SQL Server staging tables, so we can not use CLR functions or SQL functions.

I will let you know how it performs, I'll try to use the horizontal tab (ASCII code 9) as the special character because this character will be replaced by a single space in this same transformation step. I'm curious about the performance in this context, but I must admit that your solution is much more elegant than my initial version with the 65, 33, 17, 9, 5, 3 an 2 spaces nested REPLACE construction. Your method can be applied on very wide columns without a limit to the number of contiguous spaces, and all it needs is one special character.

We can use the same expression for every string column that needs to be cleansed and we only need to replace the column name once. This is an aspect of your method that comes in handy in real life situations where you need to cleans more than a few columns of a few tables.

Keep up the good work!

Dony.

Paul White (11/18/2009)

---

Hey everyone, just a quick note before I go to work this morning. I love the way this thread it going - huge thanks especially to C# Screw (lol!) and Flo for tidying my (still slightly amateur) C# as usual! I have a fairly busy morning ahead but will look in this afternoon - no doubt by then someone will have found an improvement which runs in negative time

I hope Jeff pops by soon because I'm really interested in the huge performance difference (10x) I found working with the different collations with his cool method.

Paul