Posted by Joe Cool on 02/16/07 00:39

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"

[Back to original message]