LIKE operator problem in Danish_Norwegian colation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LIKE operator problem in Danish_Norwegian colation

Author

Topic

sito
Starting Member

5 Posts

Posted - 2005-10-14 : 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

22859 Posts

Posted - 2005-10-14 : 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

49 Posts

Posted - 2005-10-14 : 14:34:36

why are using them in "[" brackets, is there any specific reason fro that.

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 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

49 Posts

Posted - 2005-10-14 : 15:05:25

oh yea, I thought it in a different way

sito
Starting Member

5 Posts

Posted - 2005-10-14 : 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

1961 Posts

Posted - 2005-10-15 : 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

22859 Posts

Posted - 2005-10-15 : 06:26:26

Aagh - or should that be ågh!

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-15 : 06:32:40

[code]
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
[/code]
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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-15 : 06:42:36

I thought there was a certain sense of deja vu about this thread...
http://sqlteam.com/Forums/topic.asp?TOPIC_ID=19817

sito
Starting Member

5 Posts

Posted - 2005-10-16 : 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

1961 Posts

Posted - 2005-10-16 : 09:20:20

[code]
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
[/code]
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources