Post reply

Ltrim rtrim no working with UPDATE

  • February 23, 2009 at 5:23 am

    #200680

    Hi Friends,

    I am facing an Issue while writing an update statement. The statement is like below

    update tablename

    set colname = ltrim(rtrim(colname)

    where filter condition

    the colname is of type CHAR.

    trying to clean up the data and cant change the data type of the column.

    Please advice.

    Regards,

    Sriram

    Sriram

  • February 23, 2009 at 5:31 am

    #948516

    if you declare a char(50), and put a single character in it, because of it's datatype it will add 49 spaces on the end.

    that's the expected behavior.

    Varchar(50), on the other hand, will behave as you expect...trim it, and it will not contain spaces at the end.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 23, 2009 at 5:34 am

    #948517

    Lowell,

    Thats the expected behavoiur fine, but the point here is, maybe for example I can put it like below. The column is of length 5. and I have more than 10 k records in that.

    the result that i get for few records when i do a copy paste is

    'WDT'

    ' WDT '

    Regards,

    Sriram

    Sriram

  • February 23, 2009 at 6:10 am

    #948522

    Are you sure that 1st character is a normal space (character code 32)?

    declare @s1 char(10), @s2 char(10)

    select @s1 = CHAR(160) + 'WDT' + CHAR(160)

    select @s1, '[' + LTRIM(RTRIM(@s1)) + ']'

    select @s2 = CHAR(32) + 'WDT' + CHAR(32)

    select @s2, '[' + LTRIM(RTRIM(@s2)) + ']'

  • February 23, 2009 at 6:19 am

    #948528

    the problem that I am facing with this is application is using colname= 'WDT' and I have 10 records against that value out of which 9 were inserted earlier and 1 has been recently inserted. now I am getting only 9 records in the app instead of 10 which is creating an issue. and cannot do a code change in the app. so trying to update the data, all in vein 🙁

    Sriram

  • February 23, 2009 at 6:37 am

    #948535

    tried to directly update with 'WDT' even that is not wokring for that records. running out of ideas.

    Sriram

  • February 23, 2009 at 6:38 am

    #948537

    That's why Andrew said to check and make sure that what look like spaces, actually are... it's a data problem... the trim functions will not remove all white-space characters... just spaces.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2009 at 6:45 am

    #948541

    What do you get if you run this (after changing columns and table names appropriately)?

    SELECT DISTINCT

    [Code] = ASCII(SUBSTRING(LTRIM(columnName), 1, 1)),

    [Character] = '[' + SUBSTRING(LTRIM(columnName), 1, 1) + ']'

    FROM tableName

    WHERE (columnName LIKE '%WDT%')

  • February 23, 2009 at 6:48 am

    #948543

    I get he below sol.

    87[W]

    Sriram

  • February 23, 2009 at 6:50 am

    #948544

    I would do the following to find the offending values

    SELECT colname,CAST(colname as varbinary(5))

    FROM tablename

    WHERE colname LIKE '%WDT'

    AND colname <> 'WDT'

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 23, 2009 at 6:54 am

    #948546

    And this should correct the data....

    UPDATE #tablename

    SET colname = STUFF(colname,1,1,'')

    WHERE colname LIKE '%WDT'

    AND colname <> 'WDT'

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 23, 2009 at 6:56 am

    #948547

    I have a space on the right side so changed the query accordingly . but afraid it shows no records.

    SELECT DISTINCT

    [Code] = ASCII(right(LTRIM(valuecode), 1)),

    [Character] = '[' + right(LTRIM(valuecode), 1) + ']'

    FROM ListOfValues

    WHERE (valuecode LIKE '%WDT%')

    -- 32,[ ]

    SELECT valuecode,CAST(valuecode as varbinary(4))

    FROM ListOfValues

    WHERE valuecode LIKE 'WDT%'

    AND valuecode <> 'WDT'

    -- No Records

    Sriram

  • February 23, 2009 at 7:01 am

    #948552

    You need to do

    LIKE '%WDT'

    not

    LIKE 'WDT%'

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 23, 2009 at 7:08 am

    #948559

    Yes David, I have done that rather tried both 1st time only, it returned no records.

    Sriram

  • February 23, 2009 at 7:23 am

    #948579

    OK. Maybe there are trailing non printable chars as well, should have thought of that :blush:.

    Try

    LIKE '%WDT%'

    Far away is close at hand in the images of elsewhere.
    Anon.

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

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