Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    13

    Unanswered: what is this, bug?

    First of all I have to say that I am somewhat of a newbie on SQL server but this can't be correct.

    Can anyone try this.

    Create a new DB (testDB)

    run this script in the DB
    -------------------------------------------------
    CREATE TABLE [dbo].[testtable] (
    [testcol] [char] (10) COLLATE Latin1_General_CI_AI NULL
    ) ON [PRIMARY]
    GO
    insert into testtable(testcol) values('ae')
    go
    insert into testtable(testcol) values('')
    go
    select * from testtable where testcol like 'ae'
    go
    ------------------------
    Do you get 2 rows returned as I did?

    This cant be right, can it?
    As anyone from scandinavia (like me) can tell you 'ae' is not the same as '' ( I really hope this will show correctly in other browsers)
    even thought they look quite simular.
    I'm sure it's a codepage thing, but is there any way to change the codepade to the right one (the one that gives 1 row for the select statement above) in a db already in production with data in it?
    In my case i need that column to be a primary key....

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    This was a tuff one!

    You are correct in your assumption. Using the "Latin1_General_CI_AI" collation somehow translates the single character '' as equal to 'ae'. When I re-created your table with my default collation 'SQL_Latin1_General_CP1_CI_AS' the select worked as expected. You won't be able to create a unique index on this table, I tried. So you have some choises to make. If you want to change the collation you can just alter the table:

    code:
    --------------------------------------------------------------------------------------------------
    Alter TABLE testtable alter column testcol char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    --------------------------------------------------------------------------------------------

    However I don't know what effect this will have regarding the rest of your data or apps.

    Interestingly enough if I change the data type to nchar and use my default colaltion I have the same problem!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •