Search with %t%y% => Error, %ty% => ok...

Search with %t%y% => Error, %ty% => ok...

• DNAGCAT

  I tested this and it worked fine for me.

  Please give the rows in your table as well.

  Regards,

  Anjana

  
  
  

• DevboyX

  Hi Mikael,

   

  I have forwarded your question to the private MVP group. Here's the answer:

   

  Documented in the nearest "Hungarian for Beginners" you can find.

   

  In Hungarian Y is not a letter in the alphabet, but GY, LY and TY
  are. Below is an extended version that also demonstrates this for
  Traditional Spanish where CH also was a letter. It also shows the
  same behaviour for SZ in Hungarian.

   

  
   
  CREATE TABLE [TEST] (
   [ID] [int] NOT NULL ,
   [NAME1] [varchar] (100) COLLATE Hungarian_CI_AS NULL ,
   [NAME2] [varchar] (100) COLLATE Czech_CI_AS NULL,
   [NAME3] [varchar] (100) COLLATE Traditional_Spanish_CI_AS NULL
  ) ON [PRIMARY]
  GO

   

  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (1,'AB Style','')
  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (2,'Lmt Storayle','')
  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (3,'','AB Style')
  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (4,'','Lmt Storayle')

   

  INSERT TEST (ID, NAME1, NAME3) VALUES (5, 'Szeged', 'Cochabamba')
  INSERT TEST (ID, NAME1, NAME3) VALUES (5, 'Cochabamba', 'Szeged' )

   

  
  SELECT * FROM TEST WHERE NAME1 LIKE '%t%y%'
  SELECT * FROM TEST WHERE NAME1 LIKE '%s%z%'
  SELECT * FROM TEST WHERE NAME1 LIKE '%c%h%'
  SELECT * FROM TEST WHERE NAME3 LIKE '%s%z%'
  SELECT * FROM TEST WHERE NAME3 LIKE '%c%h%'
  go
  drop table TEST
  

   

  There are some other Hungarian collation "gotchas" as well.

  The Hungarian letter sz, for example, can be written in
  three cased versions: 'sz', 'SZ', or 'Sz', all of which compare
  as equal to each other, but 'sZ' is not equal to the other three.
  In addition, a double sz is ssz.

   

  If you don't know how Hungarian works, you would probably
  think it was a bug that 'ssz' LIKE 'szsz' is true, but
  'ssz' LIKE 'ss' is false.

   

  Here's a fun repro:

   

  CREATE TABLE [TEST] (
    [ID] [int] NOT NULL ,
    [NAME1] [varchar] (10) COLLATE Hungarian_CI_AS NOT NULL
  ) ON [PRIMARY]
  GO

   

  INSERT INTO TEST (ID, NAME1) VALUES (1,'ssz')
  INSERT INTO TEST (ID, NAME1) VALUES (2,'sSz')
  INSERT INTO TEST (ID, NAME1) VALUES (3,'SsZ')

   

  SELECT * FROM TEST WHERE NAME1 LIKE '%szsz%'
  SELECT * FROM TEST WHERE NAME1 LIKE '%sz%sz%'
  SELECT * FROM TEST WHERE NAME1 LIKE '%sZSz%'
  SELECT * FROM TEST WHERE NAME1 LIKE '%szSz%'
  SELECT * FROM TEST WHERE NAME1 LIKE '%ss%'

   

  go

   

  drop table TEST

   

  
  One of these days, I'll see if the Hungarian collation has
  any interesting behavior for the one 3-glyph Hungarian
  letter dzs, as in dzsungel.
  

   

   

  
  --
  Hugo Kornelis, SQL Server MVP

   

  <Mikael....@discussions.microsoft.com> schreef in bericht news:627935d0-16b2-4bfd-a039-0c7b28f975c9@discussions.microsoft.com...

  Ok, I think I am getting closer to understanding, but still:

  If I make an insert into the table and enter y in NAME1 column, this is interpreted as something. Then when I make a select, shouldn't my y be interpreted as the same something, and therefore give me the correct result

  Another question: does the collate command "convert" my select to the specified codepage, if so, shouldn't SELECT * FROM TEST WHERE NAME1 LIKE '%t%y%' COLLATE Hungarian_CI_AS work (It does not).

  Thank you for trying to explain to me :)

  
  

• inblosam

  Ok, I think I am getting closer to understanding, but still:

  If I make an insert into the table and enter y in NAME1 column, this is interpreted as something. Then when I make a select, shouldn't my y be interpreted as the same something, and therefore give me the correct result

  Another question: does the collate command "convert" my select to the specified codepage, if so, shouldn't SELECT * FROM TEST WHERE NAME1 LIKE '%t%y%' COLLATE Hungarian_CI_AS work (It does not).

  Thank you for trying to explain to me :)

  
  

• havoc27

  It's just that if you collate it to Hungarian_CI_AS it's is interpreted differently.

  in your second query where you are using "collate Czech_CI_AS", try using some other collation, even that would work.

  For example test this with name2 column instead of name1 to observe better

  Query 1: SELECT * FROM TEST WHERE NAME2 LIKE '%t%y%'

  Result: Correct results

  Query 2: SELECT * FROM TEST WHERE NAME2 LIKE '%t%y%' collate Czech_CI_AS

  Result: Correct results

  Query 3: SELECT * FROM TEST WHERE NAME2 LIKE '%t%y%' collate Hungarian_CI_AS

  Result: Wrong results

  Regards,

  Anjana

  
  
  

• Andrew Robinson

  Hi!

  If you try the following you will see my error:

  CREATE TABLE [TEST] (
   [ID] [int] NOT NULL ,
   [NAME1] [varchar] (100) COLLATE Hungarian_CI_AS NOT NULL ,
   [NAME2] [varchar] (100) COLLATE Czech_CI_AS NOT NULL
  ) ON [PRIMARY]
  GO

  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (1,'AB Style','')
  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (2,'Lmt Storayle','')
  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (3,'','AB Style')
  INSERT INTO TEST (ID, NAME1, NAME2) VALUES (4,'','Lmt Storayle')

  SELECT * FROM TEST WHERE NAME1 LIKE '%t%y%'

  => Will find entry 2 (should have found entry 1 as well).

  
  SELECT * FROM TEST WHERE NAME2 LIKE '%t%y%'

  => Will find entry 3 and 4.

  So my problem is some sort of code page error...

  But if we try:

  SELECT * FROM TEST WHERE NAME1 LIKE '%t%y%' collate Czech_CI_AS

  => Finds entry 1 and 2.

  I do not understand this at all. If I use some codepage and enter "y" and then make a select ...like 'y'..why does this result not show then. Even if the "y" is interpreted as something else (some other unicode), then the interpretation should be the same both times.

  The last search above is for me totally illogical, why do I find the entries when I search using Czech code page when the column is using Hungarian

   

  If somebody have an explanation, please share it...

  // Mikael

  
  
• Search with %t%y% => Error, %ty% => ok...