Remove extra blank spaces in SQL

1

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

2 
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 '%  %'
3

Hi Scott,

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

4

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?

5

Yes you are right. Please remove those characters.

6 
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
7

Scott,

Works perfectly. Thank you so much.

1 Like
8

You're welcome. Thanks for the good feedback!