Posted by Ed Murphy on 02/16/07 02:27

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?

[Back to original message]