Replace multiple spaces in varchar

How can I replace multiple spaces in a varchar string?
Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
I want to find all occurrences of 2 or more spaces and replace with 1 space.
My reporting application can't handle this.
Thankstry this
replace( 'ABC DEF GHI', ' ',' ')
"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>|||--One possibility:
SELECT REPLACE(REPLACE('ABC DEF GHI JKL', ' ', ' '), ' ', ' ')
Handles up to 4 spaces
SELECT REPLACE(REPLACE(REPLACE('ABC DEF GHI JKL', ' ', '
'), ' ', ' '), ' ', ' ')
Handles up to 8 spaces
I know this works on my setup but maybe an expert will see a problem with
this.
Another (probably slower but will handle any number of spaces)
would be to build a function to handle this and integrate this logic:
DECLARE @.str VARCHAR(50)
SET @.str = 'ABC DEF GHI JKL'
WHILE LEN(@.str) > LEN(REPLACE(@.str, ' ', ' '))
SET @.str = REPLACE(@.str, ' ', ' ')
SELECT @.str
"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>|||"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>
SELECT REPLACE(REPLACE(REPLACE('ABC DEF GHI',' ','[ ]'),'][ ',''),'[ ]','
')|||The short answer is to nest REPLACE() functions inside each other.
This problem came up years ago in a Newsgroup, but we then asked the
question; What is the best series of replacement sizes to use on a
string of length(n)? For example, I could use powers of 2; reduce 8
spaces to 1, then 4 spaces to 1, and finally 2 spaces to 1, which does
not work unless you run it twice). I guessed that a Fibbonacci series
might be best but could not prove it. There were some other guesses,
but one guy worked out a list by brute force.
Anyone know where to find this? Since newbies constantly make
everything in the universe VARCHAR(50), that sounds like a good upper
value to use for experiments.|||Never mind; I found it! It is over in comp.database.theory as
"Squeezing spaces out of a string" and there is some code posted for
the solutions.|||http://tinyurl.com/cx7gt
HTH,
Gert-Jan
Terri wrote:
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1 spac
e.
> My reporting application can't handle this.
> Thanks|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1113589959.961601.150610@.f14g2000cwb.googlegroups.com...
> Never mind; I found it! It is over in comp.database.theory as
> "Squeezing spaces out of a string" and there is some code posted for
> the solutions.
>
Interesting thread. Here's a rewrite of my earlier response to take into
account overflow:
DECLARE @.s VARCHAR(8000)
SET @.s = 'a b c d' + SPACE(7989) + 'e'
SELECT
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,000
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,200
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,400
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,600
1,2000),' ','[ ]'),'][ ',''),'[ ]','
'),
SPACE(4),' '),SPACE(3),' '),SPACE(2),' ')|||Hmm, why use 15 replaces when you only need 6?
See http://tinyurl.com/cx7gt
Gert-Jan
Chris Hohmann wrote:
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1113589959.961601.150610@.f14g2000cwb.googlegroups.com...
> Interesting thread. Here's a rewrite of my earlier response to take into
> account overflow:
> DECLARE @.s VARCHAR(8000)
> SET @.s = 'a b c d' + SPACE(7989) + 'e'
> SELECT
> REPLACE(REPLACE(REPLACE(
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,000
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,200
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,400
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,600
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> '),
> SPACE(4),' '),SPACE(3),' '),SPACE(2),' ')|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426033FB.8CBC2FC0@.toomuchspamalready.nl...
> Hmm, why use 15 replaces when you only need 6?
> See http://tinyurl.com/cx7gt
> Gert-Jan
>
> Chris Hohmann wrote:
The encoding from ' ' to '[ ]' has the potential to increase the size of the
string threefold. The maximum allowable length for stings is 8000. Since
8000 is not evenly divisible by 3, the string needs to be broken into at
least four parts. Once multiple spaces have been collapsed in each of the
four parts, the resulting strings are concatenated together. However, it is
possible that one part ends with a space and the following part begins with
one. The limit case for this scenario would be a string of 8000 spaces. In
which case four spaces would be concatenated together at the end. To account
for this case, I perform the three additional replace calls after the parts
have been concatenated. You could actually get away with two replace calls
at the end instead of three and eliminate the calls to SPACE. Something like
REPLACE(REPLACE
.
.
.
' ',' '),' ',' ')
In which case there would only be 14 replace calls.
HTH
-Chris Hohmann