Replace Multiple Spaces with One Space?

[Joe Cool]

I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.

[Russ Rose]

Re: Replace Multiple Spaces with One Space?


"Joe Cool" <joecool@home.n etwrote in message
news:5pb7t2dkt3 7mgh0cqnm3f9luu u1rvhglkm@4ax.c om...

>I need to map several columns of data from one database to another
where the data contains multiple spaces (once occurance of a variable
number or spaces) that I need to replace with a single space. What
would be the most efficient way to do this? I am using SQL2K. I was
thinking a function since I know of no single Transact-SQL command
that can accomplish this task.

DECLARE @FieldName varchar(5000)
SET @FieldName = ' Reduce any number of' + SPACE(512) +
'spaces up to 512 in
a row to a single space
'

--You can reduce layers of REPLACE depending how many extra spaces you
expect.

SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
')

[Joe Cool]

Re: Replace Multiple Spaces with One Space?

On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@hotma il.com>
wrote:

>
>"Joe Cool" <joecool@home.n etwrote in message
>news:5pb7t2dkt 37mgh0cqnm3f9lu uu1rvhglkm@4ax. com...

>>I need to map several columns of data from one database to another
>where the data contains multiple spaces (once occurance of a variable
>number or spaces) that I need to replace with a single space. What
>would be the most efficient way to do this? I am using SQL2K. I was
>thinking a function since I know of no single Transact-SQL command
>that can accomplish this task.

>
>DECLARE @FieldName varchar(5000)
>SET @FieldName = ' Reduce any number of' + SPACE(512) +
>'spaces up to 512 in
>a row to a single space
>'
>
>--You can reduce layers of REPLACE depending how many extra spaces you
>expect.
>
>SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
>')
>

I believe you misunderstood my question. Here are some sample values:

"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)

Desired result"

"SMITH JR"
"JONES JR"

[Ed Murphy]

Re: Replace Multiple Spaces with One Space?

Joe Cool wrote:

On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@hotma il.com>
wrote:
>

>"Joe Cool" <joecool@home.n etwrote in message
>news:5pb7t2dkt 37mgh0cqnm3f9lu uu1rvhglkm@4ax. com...

>>I need to map several columns of data from one database to another
>>where the data contains multiple spaces (once occurance of a variable
>>number or spaces) that I need to replace with a single space. What
>>would be the most efficient way to do this? I am using SQL2K. I was
>>thinking a function since I know of no single Transact-SQL command
>>that can accomplish this task.

>DECLARE @FieldName varchar(5000)
>SET @FieldName = ' Reduce any number of' + SPACE(512) +
>'spaces up to 512 in
>a row to a single space
>'
>>
>--You can reduce layers of REPLACE depending how many extra spaces you
>expect.
>>
>SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '), SPACE(2), '
>')
>>

>
I believe you misunderstood my question. Here are some sample values:
>
"SMITH JR" (5 spaces)
"JONES JR" (7 spaces)
>
Desired result"
>
"SMITH JR"
"JONES JR"

Looks to me like his answer produces the desired result. What problem
do you see with it?

[Russ Rose]

Re: Replace Multiple Spaces with One Space?


"Ed Murphy" <emurphy42@soca l.rr.comwrote in message
news:45d51798$0 $28101$4c368faf @roadrunner.com ...

Joe Cool wrote:
>

>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@hotma il.com>
>wrote:
>>

>>"Joe Cool" <joecool@home.n etwrote in message
>>news:5pb7t2dk t37mgh0cqnm3f9l uuu1rvhglkm@4ax .com...
>>>I need to map several columns of data from one database to another
>>>where the data contains multiple spaces (once occurance of a variable
>>>number or spaces) that I need to replace with a single space. What
>>>would be the most efficient way to do this? I am using SQL2K. I was
>>>thinking a function since I know of no single Transact-SQL command
>>>that can accomplish this task.
>>DECLARE @FieldName varchar(5000)
>>SET @FieldName = ' Reduce any number of' + SPACE(512) +
>>'spaces up to 512 in a
>>row to a single space '
>>>
>>--You can reduce layers of REPLACE depending how many extra spaces you
>>expect.
>>>
>>SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
>>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '),
>>SPACE(2), ' ')

>>
>I believe you misunderstood my question. Here are some sample values:
>>
>"SMITH JR" (5 spaces)
>"JONES JR" (7 spaces)
>>
>Desired result"
>>
>"SMITH JR"
>"JONES JR"

>
Looks to me like his answer produces the desired result. What problem
do you see with it?

It was a bit of overkill... maybe this will be closer to what you want.


CREATE FUNCTION dbo.SingleSpace (@str varchar(8000))

RETURNS varchar(8000)

AS
--Usage
--SELECT dbo.SingleSpace ('Function replaces any and all spaces up
to 16 in a row with a single space ')

BEGIN
DECLARE @s varchar(8000)
SELECT @s = REPLACE(REPLACE (REPLACE(RTRIM( LTRIM(@str)), SPACE(4), ' '),
SPACE(2), ' '), SPACE(2), ' ')
RETURN @s
END

[Ed Murphy]

Re: Replace Multiple Spaces with One Space?

Russ Rose wrote:

"Ed Murphy" <emurphy42@soca l.rr.comwrote in message
news:45d51798$0 $28101$4c368faf @roadrunner.com ...

>Joe Cool wrote:
>>

>>On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@hotma il.com>
>>wrote:
>>>
>>>"Joe Cool" <joecool@home.n etwrote in message
>>>news:5pb7t2d kt37mgh0cqnm3f9 luuu1rvhglkm@4a x.com...
>>>>I need to map several columns of data from one database to another
>>>>where the data contains multiple spaces (once occurance of a variable
>>>>number or spaces) that I need to replace with a single space. What
>>>>would be the most efficient way to do this? I am using SQL2K. I was
>>>>thinking a function since I know of no single Transact-SQL command
>>>>that can accomplish this task.
>>>DECLARE @FieldName varchar(5000)
>>>SET @FieldName = ' Reduce any number of' + SPACE(512) +
>>>'spaces up to 512 in a
>>>row to a single space '
>>>>
>>>--You can reduce layers of REPLACE depending how many extra spaces you
>>>expect.
>>>>
>>>SELECT REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(RTRIM (LTRIM(@FieldNa me)),
>>>SPACE(16), ' '), SPACE(8), ' '), SPACE(4), ' '), SPACE(2), ' '),
>>>SPACE(2), ' ')
>>I believe you misunderstood my question. Here are some sample values:
>>>
>>"SMITH JR" (5 spaces)
>>"JONES JR" (7 spaces)
>>>
>>Desired result"
>>>
>>"SMITH JR"
>>"JONES JR"

>Looks to me like his answer produces the desired result. What problem
>do you see with it?

>
It was a bit of overkill... maybe this will be closer to what you want.
>
>
CREATE FUNCTION dbo.SingleSpace (@str varchar(8000))
>
RETURNS varchar(8000)
>
AS
--Usage
--SELECT dbo.SingleSpace ('Function replaces any and all spaces up
to 16 in a row with a single space ')
>
BEGIN
DECLARE @s varchar(8000)
SELECT @s = REPLACE(REPLACE (REPLACE(RTRIM( LTRIM(@str)), SPACE(4), ' '),
SPACE(2), ' '), SPACE(2), ' ')
RETURN @s
END

Doesn't work on 11, 14, or 15 spaces.

Adding an extra REPLACE(..., SPACE(2), ' ') wrapper will fix it, and
extend it to runs up to 26.

Adding an inner 8->1 on top of that extends to 167; 16->1 on top of that
extends to 2447; 32->1 on top of that extends to 77343, more than enough
unless you're messing with TEXT (for which REPLACE doesn't work at all,
IIRC) or VARCHAR(MAX).

[--CELKO--]

Re: Replace Multiple Spaces with One Space?

Do an UPDATE statement with nested REPLACE() functions to change (n)
spaces to 1 space:

UPDATE Foobar
SET mystring = REPLACE (SPACE(2), SPACE(1) ' ,
...
REPLACE (SPACE(<<fib(n) >>), SPACE(1),
mystring)
.. )));

The optimal pattern for the substitutions is a Fibbonnaci series with
the longest string of spaces in the innermost invocation. You have to
pick the right number based on the length of the column. Working out
the math is fun, so enjoy.

[Blackburn]

Re: Replace Multiple Spaces with One Space?

"Russ Rose" <russrose@hotma il.comwrote:

>

How did you guys miss the recursion boat?

Create function dbo.udf_Condens eSpaces (@str varchar(8000))

Returns varchar(8000)
AS

BEGIN
Declare @s varchar(8000)

Set @s = replace(@str, ' ', ' ')

if charindex(' ', @s) 0

set @s = dbo.udf_Condens eSpaces(@s)

return @s
END

[MikeJ]

Re: Replace Multiple Spaces with One Space?

also instead of calling a function many times
create a function or a proct
Sample incomming string

declare @somestring varchar(100)
set @somestring = 'aaa bbb cccc eee fff f gggg h i aa '
--sample guts of function / proc
while charindex(' ',@somestring)> 0 begin
set @Somestring = replace(@somest ring,' ',' ')
end
print @somestring
---output aaa bbb cccc eee fff f gggg h i aa




"Blackburn" <blackburn@2cen tsediting.mypan ts.comwrote in message
news:1192727141 _459@sp12lax.su perfeed.net...

"Russ Rose" <russrose@hotma il.comwrote:

>>

>
>
How did you guys miss the recursion boat?
>
Create function dbo.udf_Condens eSpaces (@str varchar(8000))
>
Returns varchar(8000)
AS
>
BEGIN
Declare @s varchar(8000)
>
Set @s = replace(@str, ' ', ' ')
>
if charindex(' ', @s) 0
>
set @s = dbo.udf_Condens eSpaces(@s)
>
return @s
END