[AccessD] Replace multiple spaces with a single space

Hi Stuart

I like the combination ".. a safe substitution character which shouldn't appear .."
Either it is safe and will not appear, or it is unsafe because it shouldn't appear but you don't know for sure.

And as Max, I just replace directly with a single space for such tasks. Why wouldn't (shouldn't) that work with TSQL?

/gustav


>>> stuart at lexacorp.com.pg 16-11-2009 11:11 >>>
An rather neat expression to get rid of extra spaces in strings
i.e.   Convert "This          string             has     too       many      spaces" to
"This string has to many spaces" 

Based on an TSQL set based method in the latest SQLServerCentral newsletter.
Can either be used directly as an expression in a  query or you could create a single line 
function for it (inline in a query should be more efficient)

'uses chr$(7) as a safe substitution character which shouldn't appear in the original
'1. replaces "space + space" with  "space + Chr$(7)"
'2. replaces "chr$(7) + space" with an empty strings
'3. replaces left over chr$(7)s with empty strings

ReduceSpaces = Replace(Replace(Replace(Trim(OriginalString), "  ", " " _
               + Chr$(7)), Chr$(7) + " ", ""), Chr$(7), "")

Can be used almost unchanged in a TSQL stored procedure - just replace Chr$() with 
CHAR() and Trim() with LTRIM(RTRIM())  


-- 
Stuart