May 21, 2008 at 11:29 am
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.
May 21, 2008 at 11:43 am
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
Consultant - Straight Path Solutions
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
May 21, 2008 at 11:54 am
it is a varchar (40).
May 21, 2008 at 12:04 pm
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
Consultant - Straight Path Solutions
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
May 21, 2008 at 12:30 pm
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
May 21, 2008 at 12:42 pm
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
Consultant - Straight Path Solutions
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
May 21, 2008 at 12:45 pm
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'
May 21, 2008 at 12:45 pm
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
May 21, 2008 at 12:49 pm
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
May 21, 2008 at 12:55 pm
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:
[font="Courier New"]SELECT collation_name
FROM sys.databases
WHERE database_id = DB_ID()
[/font]
Jack Corbett
Consultant - Straight Path Solutions
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
May 21, 2008 at 1:06 pm
i get SQL_Latin1_General_CP1_CI_AS
May 21, 2008 at 1:20 pm
Okay that is good. It means your database was setup to be case insensitive, now we need to know if for some reason your productname column was created with a a case-sensitive collation and you do that with this code:
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID('products')
AND name = 'productname'
I still would like to see some example data and queries so we can find the root cause of the issue. The Update RTRIM will likely fix the issue, but there is something else going on that will remain hidden without some examples.
Jack Corbett
Consultant - Straight Path Solutions
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
May 21, 2008 at 1:39 pm
I get an empty set when running your query.
Another example: i found two productnames:
"ComputerAA1" and "ComputerAA1 "
I tried to do uan update to the second one
Update Products
set productname= 'ComputerAA1' where productname= 'ComputerAA1 '
I would get a constraint error for duplicates.
In which another question arises how come it let me add productname= 'ComputerAA1 ' if 'ComputerAA1' already existed? and there is a constraint for duplicates?
I guess sql understands 'ComputerAA1' and productname= 'ComputerAA1 ' as two different entities.
would len(ProductName + 'x')-1 also count if there is a tab at the end?
May 21, 2008 at 2:53 pm
DBA (5/21/2008)
I get an empty set when running your query.
My fault on the empty set. In the Object_ID function you need to schema qualify the table and then add the object type like this:
Object_ID('dbo.products', 'table')
DBA (5/21/2008)
Another example: i found two productnames:"ComputerAA1" and "ComputerAA1 "
I tried to do uan update to the second one
Update Productsset productname= 'ComputerAA1' where productname= 'ComputerAA1 '
I would get a constraint error for duplicates.
In which another question arises how come it let me add productname= 'ComputerAA1 ' if 'ComputerAA1' already existed? and there is a constraint for duplicates?
I guess sql understands 'ComputerAA1' and productname= 'ComputerAA1 ' as two different entities.
You finally hit on it at the end of your statement. SQL Server treats special whitespace characters (Tab, LF, Carriage Return) different from spaces. Thus 'CompterAA1' = 'ComputerAA1 ' (single space) but 'ComputerAA1' != 'ComputerAA1 ' (non-space whitespace character). Basically this means that a character column with trailing spaces will cause a unique constraint error, but a character column with trailing special whitespace characters will not. Thus you will need to work on mapping matching products to a single product without trailing whitespace and then delete the "duplicate" ones with whitespace.
So in your issue you have trailing whitespace, not spaces. Odds are your whitespace is one or more of the following (tab, carriage return, line feed) which you can find using the CHAR() function. Tab is CHAR(9), Line Feed CHAR(10), and Carriage Return CHAR(13). The code to find them would be like this:
[font="Courier New"]SELECT
*
FROM
products
WHERE
CHARINDEX(CHAR(9), productname) > 0 OR -- tab
CHARINDEX(CHAR(10), productname) > 0 OR -- line feed
CHARINDEX(CHAR(13), productname) > 0 -- carriage return[/font]
DBA (5/21/2008)
would len(ProductName + 'x')-1 also count if there is a tab at the end?
Yes it does as does DataLength.
Jack Corbett
Consultant - Straight Path Solutions
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
May 21, 2008 at 3:17 pm
would the RTRIM function also get rid of the whitespaces like tab?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply