Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help with Trim Expand / Collapse
Author
Message
Posted Wednesday, May 21, 2008 11:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
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.


Post #504713
Posted Wednesday, May 21, 2008 11:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 11,264, Visits: 13,022
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

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
Post #504720
Posted Wednesday, May 21, 2008 11:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
it is a varchar (40).
Post #504724
Posted Wednesday, May 21, 2008 12:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 11,264, Visits: 13,022
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

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
Post #504729
Posted Wednesday, May 21, 2008 12:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #504743
Posted Wednesday, May 21, 2008 12:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 11,264, Visits: 13,022
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

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
Post #504750
Posted Wednesday, May 21, 2008 12:45 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
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'


Post #504754
Posted Wednesday, May 21, 2008 12:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #504755
Posted Wednesday, May 21, 2008 12:49 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #504758
Posted Wednesday, May 21, 2008 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 11,264, Visits: 13,022
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

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
Post #504763
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse