Posted by Russ Rose on 02/17/07 20:56

"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:45d51798$0$28101$4c368faf@roadrunner.com...
> Joe Cool wrote:
>
>> On Wed, 14 Feb 2007 20:47:37 -0600, "Russ Rose" <russrose@hotmail.com>
>> wrote:
>>
>>> "Joe Cool" <joecool@home.net> wrote in message
>>> news:5pb7t2dkt37mgh0cqnm3f9luuu1rvhglkm@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(REPLACE(REPLACE(RTRIM(LTRIM(@FieldName)),
>>> 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

[Back to original message]