Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Trim


Help with Trim

Author
Message
DBA-640728
DBA-640728
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1993
hi guys, can anyone tell me the best way to trim spaces after a name in my column productname in my products table? I did an export from access to sql server; for some reason the names were imported with spaces at the end and it creating lots of errors since the users cannot find the products by products name.
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11022 Visits: 14858
Is your column defined a fixed length (char/nchar) or variable length (varchar/nvarchar)? If fixed length you would be better off to right pad any text out to the length of the column than using a RTRIM on the column.

For variable length columns you should use RTRIM on insert or update. Check out this thread or this blog post to see how trailing spaces are handled in variable length columns.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
DBA-640728
DBA-640728
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1993
it is a varchar (40).
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11022 Visits: 14858
Can you post an example like noted in the article referenced in my signature? If you are using equality operators you should find the matching products. There may be an issue with like though.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
The quick answer is update the table using RTrim.


update dbo.Table
set Column = rtrim(Column)



Since you're using Varchar data type, that will probably do it.

If it doesn't, the thing to do is take a look at ANSI_PADDING in Books Online and start checking for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11022 Visits: 14858
GSquared (5/21/2008)
The quick answer is update the table using RTrim.


update dbo.Table
set Column = rtrim(Column)



Since you're using Varchar data type, that will probably do it.

If it doesn't, the thing to do is take a look at ANSI_PADDING in Books Online and start checking for that.


G,

I considered giving this answer as well, but I really want to know why queries are not returning data as in all my tests from the other thread I referenced in my first post, I could not get an equality operator to fail on a varchar column with trailing spaces. 'Jack' = 'Jack ' or 'Jack ' or 'Jack' if ANSI_PADDING was on or off. Based on those tests I would not expect trailing spaces to cause a problem, so I am thinking it might be a Case-Sensitive installation.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
DBA-640728
DBA-640728
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1993
queries not returning data are for example as follows

SELECT p.productname

FROM Products P
WHERE p.productname= 'Computer233-A' and Active = '0'



where in so productname 'Computer233-A' does exist in the table however if we look by 'Computer233-A ' then it is found.




SELECT len(p.productname) as Length, p.productname, p.productnumber,p.productID_PK AS ID

FROM Products P
WHERE p.Active = '0'




(p.productname) = varchar (40)

question, i know that with len(p.productname) i only get the count for characters without the spaces, how can i get the count with spaces?

would this work?


UPDATE Products SET Products.ProductName = RTrim([Products].[ProductName])
FROM Products P
WHERE p.Active = '0'



GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Jack: Yeah, I saw your posts, and didn't want to be redundant on that point. I totally agree with you, just wanted to add a couple of possibly pertinent other points that hadn't been posted yet.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
DBA (5/21/2008)


SELECT len as Length, p.productnumber,p.productID_PK AS ID

FROM Products P
WHERE p.Active = '0'




(p.productname) = varchar (40)

question, i know that with len(p.productname) i only get the count for characters without the spaces, how can i get the count with spaces?

would this work?


UPDATE Products SET Products.ProductName = RTrim([Products].[ProductName])
FROM Products P
WHERE p.Active = '0'




To get the count with spaces, do something like "len(productname + 'x')-1".

The second update should work, but you really don't need the From part of it. Just delete that line. (It will work either way, but the From isn't needed in this case.)

As mentioned already, this may or may not actually solve your problem. To most SQL databases "xx " = "xx" (with or without trailing spaces). Try the rtrim update, see if that does it, but don't be too shocked if it doesn't.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11022 Visits: 14858
DBA (5/21/2008)


SELECT len as Length, p.productnumber,p.productID_PK AS ID

FROM Products P
WHERE p.Active = '0'




(p.productname) = varchar (40)

question, i know that with len(p.productname) i only get the count for characters without the spaces, how can i get the count with spaces?

would this work?


UPDATE Products SET Products.ProductName = RTrim([Products].[ProductName])
FROM Products P
WHERE p.Active = '0'







You get the actual stored bytes using the DATALENGTH function. I said bytes because for UNICODE (nchar/ncarchar) it returns 2 bytes for each character.

Yes your code will work although you can just do what GSquared posted you do not need the From clause just the update and where clause.

This still does not explain why queries are not returning data. What do you get back when you run this code in the database where you are having issues:

SELECT collation_name
    
FROM sys.databases
    
WHERE database_id = DB_ID()
            






Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search