Cleaning up excess contiguous spaces in strings. A mystery.

Phil Factor, 2010-09-30

/* Have you ever come across the problem where you have to remove spaces from a string that has too many? You’ll know that if you do the obvious …*/
SELECT REPLACE (‘this         has            too                          many                               spaces’ ,‘  ‘, ‘ ‘)
— … you end up with your problem only slightly diminished.
–this     has      too             many                spaces

/* you could, of course, just repeat this until all the extra spaces are gone, but this isn’t going to help if you need to do this rapidly as an inline process as un update to a huge table of strings. Yet, this sort of data-cleaning is done a lot.
I must admit that I’ve always used a scalar function for this, which hid an iterative process. */
DECLARE @trimmed VARCHAR(255) ,
@LastTrimmed INT

SELECT  @trimmed = ‘this    has           too  many        spaces’ ,
@LastTrimmed = 0

WHILE LEN(@Trimmed) <> @LastTrimmed
SELECT  @LastTrimmed = LEN(@Trimmed) ,
@trimmed = REPLACE(@trimmed, ‘  ‘, ‘ ‘)
SELECT  @Trimmed

/* but when I thought about it, (I was re-writing Robyn Page’s String Manipulation Workbench for her, to bring it up to date) it seemed inefficient. This is a lot better as long as you don’t have more than 208 continuous spaces. (no, you’re not going to find that in an address) */

‘this           has              too                            many                                 spaces’ ,
REPLICATE(CHAR(32), 5), CHAR(32)),
REPLICATE(CHAR(32), 4), CHAR(32)),
REPLICATE(CHAR(32), 3), CHAR(32)),

/* If you test this out, you’ll find that it will only clean out 38 contiguous spaces if you miss out the replacement for six consecutive spaces, and 10 if you miss out both the five and six consecutive spaces.
If, instead of 2 3 4 5 6, you chose 2 3 4 7 21 you get upto 461 contiguous spaces cleaned up rather then 208.
What is the maths behind this then?Is this the optimum sequence, or is there something better? (2 3 4 6 21 seems to do as well)

Help! Does anyone know of a helpful mathematician? I’d love an explanation





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads