RTRIM not working. - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

RTRIM not working.

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2011-12-09 : 11:59:39

Hello I'm trying to run a trim query on a nvarchar field, but it doesn't work. Here's what I'm trying to do:

go
UPDATE Vendor
SET LabelName = RTRIM(LabelName)
go

After the query it still looks like this:

'52 SHAKES '

Let me know what I'm doing wrong.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-09 : 12:13:23

Can you veryify what the ascii value for the "space" is? It's possible that it's 160. Perhaps try:

SELECT Replace(LabelName, CHAR(160),'')

taunt
Posting Yak Master

128 Posts

Posted - 2011-12-09 : 12:53:09

quote:
Originally posted by Lamprey

Can you veryify what the ascii value for the "space" is? It's possible that it's 160. Perhaps try:
SELECT Replace(LabelName, CHAR(160),'')

Well it did take off the majority of the spaces. it looked like there was 15 spaces after each name. So how do go about removing the CHAR(160) from it, and after will rtrim remove the space at the end?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 13:04:17

quote:
Originally posted by taunt

quote:
Originally posted by Lamprey

Can you veryify what the ascii value for the "space" is? It's possible that it's 160. Perhaps try:
SELECT Replace(LabelName, CHAR(160),'')

Well it did take off the majority of the spaces. it looked like there was 15 spaces after each name. So how do go about removing the CHAR(160) from it, and after will rtrim remove the space at the end?

Thanks

do both

SELECT LTRIM(RTRIM(Replace(LabelName, CHAR(160),'')))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

taunt
Posting Yak Master

128 Posts

Posted - 2011-12-09 : 16:14:44

How do I do a update? I tried this:

update LTRIM(RTRIM(REPLACE(LabelName, CHAR(160), '')))
FROM Vendor

and this:

UPDATE Vendortemp$
SET LTRIM(RTRIM(REPLACE(LabelName, CHAR(160), '')))

and got:

Error in list of function arguments: '(' not recognized.
Incomplete parameters or column list.
Error in table name or view name in UPDATE clause.
Unable to parse query text.

Thanks

quote:
Originally posted by visakh16

quote:
Originally posted by taunt

quote:
Originally posted by Lamprey

Can you veryify what the ascii value for the "space" is? It's possible that it's 160. Perhaps try:
SELECT Replace(LabelName, CHAR(160),'')

Well it did take off the majority of the spaces. it looked like there was 15 spaces after each name. So how do go about removing the CHAR(160) from it, and after will rtrim remove the space at the end?

Thanks

do both

SELECT LTRIM(RTRIM(Replace(LabelName, CHAR(160),'')))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-09 : 16:41:06

[code]UPDATE Vendor
SET LabelName = LTRIM(RTRIM(REPLACE(LabelName, CHAR(160), '')))[/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-09 : 18:55:49

May I ask, what is the significance of CHAR(160) versus any other non-printable character? I get that CHAR(160) is a Space with the high bit set but why are we limiting the issue to just this single character?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

dm1
Starting Member

1 Post

Posted - 2011-12-10 : 02:43:36

CHAR(160) is a Non-breaking space (http://en.wikipedia.org/wiki/Non-breaking_space), & nbsp; (without space after &) used in HTML files.

Dmitry

--
http://www.sqlines.com - Free Online SQL Conversion tool

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:53:57

quote:
Originally posted by Bustaz Kool

May I ask, what is the significance of CHAR(160) versus any other non-printable character? I get that CHAR(160) is a Space with the high bit set but why are we limiting the issue to just this single character?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

because in case you real issue is arrival of spaces which are not stripped off by TRIM functions. So assumption is its due to hard space (CHAR(160))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources