Posted by Russ Rose on 02/15/07 02:47

"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), '
')

[Back to original message]