Post reply

Handling white space in sql 2005

  • July 9, 2008 at 7:58 am

    #840166

    Thanks Michael for your suggestion.

    I'll try to convince the customers.

    Regards

    Bala

  • July 9, 2008 at 8:07 am

    #840176

    sbalaven (7/9/2008)

    Thanks Jeff.

    But i need to store them as well in the primary key column.

    Thanks and Regards

    Bala

    That would be a problem... only way I can see to do it is to replace the white space with an extended character... of course, you'd have to change that extended character back to white space if you actually need to do something with it.

    I've gotta ask... why do you need to do this? What are you building that depends on trailing white space?

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

  • July 9, 2008 at 9:24 am

    #840269

    The ANSI_Padding option would seem to allow trailing spaces to be handled the way you want them, but I just tested and it doesn't do it. It considers the strings different, but still refuses to enter them into a primary key (or unique index).

    set ansi_padding on

    create table #T (

    Val varchar(10)collate SQL_Latin1_General_CP1_CS_AS)

    create unique index UID_T_Val on dbo.t (val)

    insert into #t (val)

    select 'x'

    union all select 'x '

    drop table #T

    set ansi_padding on

    create table #T (

    Val varchar(10)collate SQL_Latin1_General_CP1_CS_AS primary key)

    insert into #t (val)

    select 'x'

    union all select 'x '

    drop table #T

    Both error out on the insert. They will allow "x" and "X" to be done, but not with a trailing space and without. The ANSI_Padding setting's description in BOL seems to say it shoud treat them as unique values because of the trailing space, but it doesn't. (It also says this property will be removed in future versions of SQL Server.)

    - 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

  • July 9, 2008 at 9:37 am

    #840286

    Thanks for your support

    Balavenkatesh

Viewing 4 posts - 16 through 18 (of 18 total)

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