Post reply

Not able to remove trailing space

  • April 10, 2012 at 12:49 am

    #259731

    Somehow i am not able to remove trailing space.

    I did try Replace, substring but no result.

    Check Constraint depend upon that column.

    (NOT [CountyFIPS] like '' AND NOT [CountyFIPS] like ' %' AND [CountyFIPS] like '[0-9][0-9][0-9][0-9][0-9]')

    select CountyFIPS

    from ##test_backfillGeo

    where CountyFIPS not like ''

    I did try..

    update ##Test_backfillGeo

    set countyfips = RTRIM(replace(countyfips,char(160),char(32)))

    update ##backfillGeo

    set CountyFIPS = ltrim(RTRIM(countyfips))

    update ##test_backfillGeo

    set CountyFIPS = replace(countyfips,' ','')

    Sample row

    48267

    48271

    48273

    48275

    48277

    Any help will be greatly appreciate.

  • April 10, 2012 at 12:57 am

    #1471076

    Whats the o/p of below query at first place?

    select CountyFIPS

    from ##test_backfillGeo

    where CountyFIPS not like ''

  • April 10, 2012 at 12:58 am

    #1471078

    I have provided sample row in previous thread.

  • April 10, 2012 at 1:25 am

    #1471085

    preetham gowda (4/10/2012)

    Whats the o/p of below query at first place?

    select CountyFIPS

    from ##test_backfillGeo

    where CountyFIPS not like ''

    Hi,

    In your query you can combine the LEN function with RTRIM and LTRIM and overpass the issue.

    EN(string_expression) return the length triming the trailing spaces.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • April 10, 2012 at 1:25 am

    #1471087

    preetham gowda (4/10/2012)

    Whats the o/p of below query at first place?

    select CountyFIPS

    from ##test_backfillGeo

    where CountyFIPS not like ''

    Hi,

    In your query you can combine the LEN function with RTRIM and LTRIM and overpass the issue.

    LEN(string_expression) returns the length triming the trailing spaces.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • April 10, 2012 at 1:25 am

    #1471086

    preetham gowda (4/10/2012)

    Whats the o/p of below query at first place?

    select CountyFIPS

    from ##test_backfillGeo

    where CountyFIPS not like ''

    Hi,

    In your query you can combine the LEN function with RTRIM and LTRIM and overpass the issue.

    LEN(string_expression) return the length triming the trailing spaces.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • April 10, 2012 at 2:30 am

    #1471105

    See if you can change the dataType from char to varchar and update the coulmn by trimming it.

  • April 10, 2012 at 2:34 am

    #1471107

    preetham gowda (4/10/2012)

    See if you can change the dataType from char to varchar and update the coulmn by trimming it.

    I did try converting Char to Varchar but getting the same error.

  • April 10, 2012 at 5:15 am

    #1471142

    EasyBoy (4/10/2012)

    preetham gowda (4/10/2012)

    See if you can change the dataType from char to varchar and update the coulmn by trimming it.

    I did try converting Char to Varchar but getting the same error.

    What error is that, exactly?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 10, 2012 at 5:17 am

    #1471143

    This has been resolved now.

  • April 10, 2012 at 7:51 am

    #1471214

    EasyBoy (4/10/2012)

    This has been resolved now.

    That's great. Now all you need to do is tell us how the problem was resolved. It may benefit others with the same or similar problem.

Viewing 11 posts - 1 through 10 (of 10 total)

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