billj
September 19, 2016, 6:07pm
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
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 '% %'
billj
September 19, 2016, 7:26pm
3
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?
billj
September 19, 2016, 7:45pm
5
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
billj
September 19, 2016, 8:35pm
7
Scott,
Works perfectly. Thank you so much.
1 Like
You're welcome. Thanks for the good feedback!