Post reply

Strange query results on different collation

  • November 8, 2011 at 7:51 am

    #247482

    Hi,

    During development I came across a strange scenario using DD with Macedonian_FYROM_90_CI_AS Collation.

    create table test_table

    ( numbers varchar(20))

    insert into test_table values('0711110000000')

    insert into test_table values('0711110000001')

    insert into test_table values('0711110000002')

    select * from test_table WHERE numbers like '071111%00000'

    It should return 0711110000000, but result from query is null.

    I try the same test on another DB using SQL_Latin1_General_CP1_CI_AS Collation and query returns 0711110000000.

    Have anyone faced with the same problem, or can explain this?

    Thanks.

    Darko

  • November 8, 2011 at 8:04 pm

    #1404945

    These types of issues tend to come down to mapping between Unicode and ASCII code points. That collation is a Unicode one, so it makes sense to use a Unicode data type. This works as expected:

    CREATE TABLE test_table

    (

    numbers NVARCHAR(20) COLLATE Macedonian_FYROM_90_CI_AS NULL

    );

    INSERT test_table

    (numbers)

    VALUES

    (N'0711110000000'),

    (N'0711110000001'),

    (N'0711110000002');

    SELECT

    *

    FROM test_table

    WHERE

    numbers LIKE N'071111%00000';

    DROP TABLE test_table;

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

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

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