| Author |
 Topic  |
|
|
sito
Starting Member
5 Posts |
 Posted - 10/14/2005 : 01:35:00
|
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
22191 Posts |
Posted - 10/14/2005 : 08:26:01
|
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 |
 |
|
|
anuj164
Starting Member
USA
49 Posts |
Posted - 10/14/2005 : 14:34:36
|
| why are using them in "[" brackets, is there any specific reason fro that. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/14/2005 : 14:59:26
|
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 |
|
|
|
anuj164
Starting Member
USA
49 Posts |
Posted - 10/14/2005 : 15:05:25
|
| oh yea, I thought it in a different way |
|
|
|
sito
Starting Member
5 Posts |
Posted - 10/14/2005 : 17:04:45
|
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
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 10/15/2005 : 06:22:21
|
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!
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/15/2005 : 06:26:26
|
Aagh - or should that be ågh!
Kristen |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 10/15/2005 : 06:32:40
|
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 |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
|
|
sito
Starting Member
5 Posts |
Posted - 10/16/2005 : 03:27:38
|
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
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 10/16/2005 : 09:20:20
|
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 |
|
|
| |
Topic |
|
LIKE operator problem in Danish_Norwegian colation
