SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 LIKE operator problem in Danish_Norwegian colation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sito
Starting Member

5 Posts

Posted - 10/14/2005 :  01:35:00  Show Profile  Reply with Quote
Hello all!

I have such a problem:

select 1 where 'aa' like '[a][a]' collate Danish_Norwegian_CI_AI
-- returns nothing

select 1 where 'ae' like '[a][e]' collate Danish_Norwegian_CS_AS
-- returns 1

It seems like first query should also return 1.

If anybody knows about this problem, please tell me.

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2005 :  08:26:01  Show Profile  Reply with Quote
Looks a bit surprising doesn't it!

select 1 where 'aa' like 'aa' collate Danish_Norwegian_CI_AI

returns 1 and

select 1 where 'aa' like '[a][a]' collate Danish_Norwegian_CI_AI

returns nothing?

Kristen
Go to Top of Page

anuj164
Starting Member

USA
49 Posts

Posted - 10/14/2005 :  14:34:36  Show Profile  Send anuj164 a Yahoo! Message  Reply with Quote
why are using them in "[" brackets, is there any specific reason fro that.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2005 :  14:59:26  Show Profile  Reply with Quote
They are "Character Classes" or whatever the correct term is

So

select 1 where 'ax' like '[abc][xyz]' collate Danish_Norwegian_CI_AI

should match a, b or c as the first character and x, y or z as the second.

but the key point is that 'a' matches 'a' OK, but 'a' does NOT match '[a]' - which is just what seems to me to be the same "a", but in a "Character Class" set

Kristen
Go to Top of Page

anuj164
Starting Member

USA
49 Posts

Posted - 10/14/2005 :  15:05:25  Show Profile  Send anuj164 a Yahoo! Message  Reply with Quote
oh yea, I thought it in a different way
Go to Top of Page

sito
Starting Member

5 Posts

Posted - 10/14/2005 :  17:04:45  Show Profile  Reply with Quote
Yes, exaktly.

It seems like this is SQL deffect.
But I try to find something that proofs it.

quote:
Originally posted by Kristen

Looks a bit surprising doesn't it!

select 1 where 'aa' like 'aa' collate Danish_Norwegian_CI_AI

returns 1 and

select 1 where 'aa' like '[a][a]' collate Danish_Norwegian_CI_AI

returns nothing?

Kristen

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/15/2005 :  06:22:21  Show Profile  Reply with Quote
select 1 where 'a' like '[å]' collate Latin1_General_CI_AI
-- returns 1

select 1 where 'a' like '[å]' collate Danish_Norwegian_CI_AI
-- returns nothing

select 1 where 'aa' like '[å]' collate Danish_Norwegian_CI_AI
-- returns 1

This may have some bearing on the problem!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/15/2005 :  06:26:26  Show Profile  Reply with Quote
Aagh - or should that be ågh!

Kristen
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/15/2005 :  06:32:40  Show Profile  Reply with Quote

SELECT *
FROM (
    SELECT 'abc' AS n UNION ALL
    SELECT 'aabc' UNION ALL
    SELECT 'åbc' UNION ALL
    SELECT 'bbc' UNION ALL
    SELECT 'zbc' UNION ALL
    SELECT 'abd' UNION ALL
    SELECT 'aabd' UNION ALL
    SELECT 'åbd' UNION ALL
    SELECT 'zbd'
  ) AS A
ORDER BY n COLLATE Danish_Norwegian_CI_AI

This might surprise people not familiar with that collation, too.
The result is (or rather, a possible result, since the ordering of values that collate equally is undefined):

abc
abd
bbc
zbc
zbd
aabc
åbc
aabd
åbd

Edited by - Arnold Fribble on 10/15/2005 06:37:34
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/15/2005 :  06:42:36  Show Profile  Reply with Quote
I thought there was a certain sense of deja vu about this thread...
http://sqlteam.com/Forums/topic.asp?TOPIC_ID=19817
Go to Top of Page

sito
Starting Member

5 Posts

Posted - 10/16/2005 :  03:27:38  Show Profile  Reply with Quote
It is not absolutly clear why we have different behavior for å and for æ symbols

select 1 where 'aa' like '[å]' collate Latin1_General_CI_AI
-- returns nothing

select 1 where 'aa' like '[å]' collate Danish_Norwegian_CI_AI
-- returns 1

------------------------------------------------

select 1 where 'ae' like '[æ]' collate Latin1_General_CI_AI
-- returns nothing

select 1 where 'ae' like '[æ]' collate Danish_Norwegian_CI_AI
-- returns nothing
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/16/2005 :  09:20:20  Show Profile  Reply with Quote

SELECT *
FROM (
    SELECT 'abc' AS n UNION ALL
    SELECT 'aabc' UNION ALL
    SELECT 'åbc' UNION ALL
    SELECT 'aebc' UNION ALL
    SELECT 'æbc' UNION ALL
    SELECT 'bbc' UNION ALL
    SELECT 'zbc' UNION ALL
    SELECT 'abd' UNION ALL
    SELECT 'aabd' UNION ALL
    SELECT 'åbd' UNION ALL
    SELECT 'aebd' UNION ALL
    SELECT 'æbd' UNION ALL
    SELECT 'zbd'
  ) AS A
ORDER BY n COLLATE Danish_Norwegian_CI_AI

can return

abc
abd
aebc
aebd
bbc
zbc
zbd
æbc
æbd
aabc
åbc
aabd
åbd

so apparently Danish_Norwegian_CI_AI considers ae to collate as separate letters, not equally to æ. As to whether that's sensible, you'd have to ask a Dane or Norwegian!
Just for completeness, ø (and equivalently ö) will collate between æ (and equivalently ä) and å.

Unlike Finnish_Swedish_CI_AI, Danish_Norwegian_CI_AI does not collate v and w equally.

Edited by - Arnold Fribble on 10/16/2005 09:26:33
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000