Help with Trim

  • DBA-640728

    SSChampion

    Points: 12896

    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

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • DBA-640728

    SSChampion

    Points: 12896

    it is a varchar (40).

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • GSquared

    SSC Guru

    Points: 260824

    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

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • DBA-640728

    SSChampion

    Points: 12896

    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

    SSC Guru

    Points: 260824

    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

    SSC Guru

    Points: 260824

    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

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • DBA-640728

    SSChampion

    Points: 12896

    i get SQL_Latin1_General_CP1_CI_AS

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • DBA-640728

    SSChampion

    Points: 12896

    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?

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 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.

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • DBA-640728

    SSChampion

    Points: 12896

    would the RTRIM function also get rid of the whitespaces like tab?

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply