Remove extra blank spaces in SQL

Hello,

I am trying to remove extra space in a string but is not working.

Here is the string.
"31 Enida 32-57^^^Manchester CITY Manchester"

Here is query.

USE TEST
UPDATE MyTable SET
Address   = replace(Address,   '31 Enida 32-57   Manchester CITY Manchester', '31 Enida 32-57 Manchester CITY Manchester')

Still there are three blank spaces
Between 32-57 and Manchester.

I think there are non printable character in the string.

Please help me.

Thanks in advance

UPDATE MyTable
SET Address = 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Address)),
        SPACE(33), SPACE(1)), SPACE(17), SPACE(1)), SPACE(9), SPACE(1)), SPACE(5), SPACE(1)),
        SPACE(3), SPACE(1)), SPACE(2), SPACE(1)), SPACE(2), SPACE(1))
WHERE Address LIKE '%  %'

Hi Scott,

Thanks for your reply. When I run your query, it return 0 row in my database.

You don't have 2 or more spaces in a row then. You probably have other characters, like carriage return, CHAR(13), and line feed, CHAR(10), and tab, CHAR(9).

Do you want to remove those characters if they appear?

Yes you are right. Please remove those characters.

UPDATE MyTable
SET Address = 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Address)),
        CHAR(9), SPACE(1)), CHAR(10), SPACE(1)), CHAR(13), SPACE(1)),
        SPACE(33), SPACE(1)), SPACE(17), SPACE(1)), SPACE(9), SPACE(1)), 
        SPACE(5), SPACE(1)),  SPACE(3), SPACE(1)), SPACE(2), SPACE(1)), 
        SPACE(2), SPACE(1))
WHERE Address LIKE '%[' + CHAR(9) + CHAR(10) + CHAR(13) + ']%'
1 Like

Scott,

Works perfectly. Thank you so much.

1 Like

You're welcome. Thanks for the good feedback!