Remove trailing spaces at the end

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Remove trailing spaces at the end

Author

Topic

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-05-15 : 12:53:01

I have a column called 'name' in table 'persons'. How do I remove the spaces at the end? I tried rtrim, but it doesn't work.

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-05-15 : 12:54:52

I tried,

UPDATE persons
SET name = RTRIM(name)

But it didn't work.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-15 : 13:59:46

Is the column CHAR or VARCHAR?

djj

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-05-15 : 14:27:11

the column is varchar

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-05-15 : 14:44:15

Then they're not actually spaces. They're like a CR (char(10)) or LF (char(13)) or Tab (char(9)) or some other non-space but non-printable character.

UPDATE persons
SET name = RTRIM(REPLACE(REPLACE(REPLACE(name, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-05-15 : 15:08:40

What is the select statement to get the list of names that are like that?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-05-16 : 12:23:08

Sorry, my bad, I mistakenly left that part off:

UPDATE persons
SET name = RTRIM(REPLACE(REPLACE(REPLACE(name, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))
WHERE
name LIKE '%' + CHAR(09) + '%' OR --tab
name LIKE '%' + CHAR(10) + '%' OR --lf
name LIKE '%' + CHAR(13) + '%' --cr

SELECT *
FROM persons
WHERE
name LIKE '%' + CHAR(09) + '%' OR --tab
name LIKE '%' + CHAR(10) + '%' OR --lf
name LIKE '%' + CHAR(13) + '%' --cr

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-16 : 12:31:16

select name
from persons
where right(name, 1) in (char(9), char(10), char(13))

There might be other non-printable characters in addition to TAB, CR and LF. You would need to expand the elements of the IN list. Alternately, instead of looking for a non-printable character, you could look for a character which not a printable character[CODE]select name
from persons
where right(name, 1) not like '[a-zA-Z0-9,./!@#$%^&*()_+-={}\|;:"'']'[/CODE]

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources