Searching for aa in Danish SQL

RolfGarde

When you use SQL then it is not possible to search for aa when the server is set to Danish-Norwegian sorting.

Example: The sorting is:
Karsten
Kåre
Kaare

When user search for Kaa - Kaare is not found. Karsten is found when searching for Ka and Kåre when searching for Kå.

Filters on *aa* will work.

Any ideas for a solution?

stryk

The collation of your database is probably "Case Insensitive", thus the system could not distinguish between "å" and "aa", the search gives no result.
When filtering, Regular Expressions are used, so it could be found.

You could try to change the collation to "Case Sensitive", but have in mind that this will be quite time consuming and probably will expand your database-size.

RolfGarde

stryk wrote:

You could try to change the collation to "Case Sensitive", but have in mind that this will be quite time consuming and probably will expand your database-size.

I tried - but that didn't help.

Any other ideas. It must be a problem in many installation !?

kine

Sometime, NAV has problem with searching, when some specific sorting is used. We have same problem with Czech sorting order and searching for character "ch" (yes, it is one character in czech). Because in czech alphabet is "ch" between "h" and "i", strings are sorted correctly on SQL. But NAV is still searching for it as for "c" and "h" and it means between characters "b" and "d". And if it is not found there, it is not found... This problem can be solved just with disabling "case sensitive" searching (the checkbox on the find dialog). If you enable this, NAV will send query to SQL in format

    '[Cc][Hh][OoÓó]...'

(for search string 'cho') to include all variants of cases. But if you disable this, it will do just

    like 'cho'

and SQL will find the correct record correctly.

May be this is same case as your, I do not know what does 'aa' mean in you alphabet.

RolfGarde

kine wrote:

May be this is same case as your, I do not know what does 'aa' mean in you alphabet.

Unfortunately we have 2 versions of the same letter. One is å (a with a ring on top) and the other is aa.

The name Aase - can be spelled with both Åse and Aase - and is not the same name. But SQL understands aa as one letter - and in most cases it is not.

todro

RolfGarde wrote:

kine wrote:

May be this is same case as your, I do not know what does 'aa' mean in you alphabet.

Unfortunately we have 2 versions of the same letter. One is å (a with a ring on top) and the other is aa.

The name Aase - can be spelled with both Åse and Aase - and is not the same name. But SQL understands aa as one letter - and in most cases it is not.

although I don't think so, it might be a Navision problem due to the fact it does not support unicode characters. Did you check directly with a select statement in the query analyzer to see how sql server natively reacts ?

What you can do: enable the client monitor with all options enabled, search for the name, verify the resulting query in the client monitor table (whether it is using the correct character) and then copy the query and execute it in the query analyzer

kine

Ok, it means that not all 'aa' are just 'a'&'a' for you... But NAV cannot know if it is just 'aa' or two 'a'. It is the source of problems, SQL will take 'aa' as one character, but NAV will understand it as two characters. And in searching for the string will assume that 'aa' is before 'ab' etc.

RolfGarde

todro wrote:

What you can do: enable the client monitor with all options enabled, search for the name, verify the resulting query in the client monitor table (whether it is using the correct character) and then copy the query and execute it in the query analyzer

Done - and the Client Monitor writes:

WHERE (("Search Name" LIKE 'K[AªÁÀÃ][AªÁÀÃ]%'))

This Query returns NULL in Qyery Analyzer. The Query

WHERE (("Search Name" LIKE 'KAA%')) returns the correct record.

Is there anything to do about this - or do Microsoft have to be involved?

todro

RolfGarde wrote:

todro wrote:

What you can do: enable the client monitor with all options enabled, search for the name, verify the resulting query in the client monitor table (whether it is using the correct character) and then copy the query and execute it in the query analyzer

Done - and the Client Monitor writes:

WHERE (("Search Name" LIKE 'K[AªÁÀÃ][AªÁÀÃ]%'))

This Query returns NULL in Qyery Analyzer.

The Query

WHERE (("Search Name" LIKE 'KAA%')) returns the correct record.

Is there anything to do about this - or do Microsoft have to be involved?

did you disable the case sensitive search ?

RolfGarde

todro wrote:

did you disable the case sensitive search ?

Yes - but that had No effect. Should i try to Disable and then the Client Monitor after?

todro

RolfGarde wrote:

todro wrote:

did you disable the case sensitive search ?

Yes - but that had No effect. Should i try to Disable and then the Client Monitor after?

yes, just to see whether the client distinguishes between w/ or w/o case sensitive.

kine

When I tried the profiler, when you disable the Case sensitive search, NAV will use just

  WHERE (("Search Name" LIKE 'KAA%'))

If you enable case sensitivity, it is searching char by char (your fisrt example) and it is wrong in this case. Do not forget that the case sensitivity is on the search dialog and it depends on the DB case sesntitivity too (on the collation used on the DB).

RolfGarde

OK. But are we any nearer a solution - or do I have to involve Microsoft to see if they have the solution?

todro

kine wrote:

When I tried the profiler, when you disable the Case sensitive search, NAV will use just

  WHERE (("Search Name" LIKE 'KAA%'))

how about disabling the "Find as you type" ? Somehow the client needs to know, how to work with "double-chars", otherwise the regular application would not work as navision doesn't support unicode natively.

So, if you enter char by char, this behaviour seems logical to me, if the "Find as you type" option is enabled. (An option, I disable on a SQL installations for performance reason anyway).

How is Navision dealing with your czech "ch", e.g. if you have a text field with a length of 10 and you enter "ch", you can enter it only 5 times ? If so, then I see this as the basic problem, even without SQL.

it depends on the DB case sesntitivity too (on the collation used on the DB).

You are right, it might depend on the collation already in the state of creating the query, but I would expect the collation to be effective simply when executing the query. In other words, I would not expect Navision to create different queries based on different collations, but simply let the SQL server decide about this, therefore being relevant for the response only (from the navision point of view).

It seems, as if I will face this situation in an actual project with a czech database, too :shock:

OK. But are we any nearer a solution - or do I have to involve Microsoft to see if they have the solution?

If there is no difference with the "Find as you type", I do not see a solution at the moment and it's worth involving Microsoft at this point.

kine

1) For us disabling case-insensitive search is enough to solve the problem. Mainly if you have case insensitive collation, because than SQL will find all cases of your string regardless of NAV setting.
2) It is common in Czech to use Latin 1 sort order with codepage 1250. In this case, ch is sorted as c and h and there is no problem (solution from times of former Navision - solution was promissed many times... :-)).
3) Find as you type will not solve the problem. But it is good to disable it to have better performance.

jorgecph

I remember dealing with this issue some time ago.

In Danish å is equivalent to aa, eventhough in some contexts this is actually different. Because of this, SQL server treats aa as å and the only workaround to this is to uncheck the accent-sensitive and use å to search for 'aa'. Notice that everything will be found except the 'aA' combination.

I am not aware of any other way of dealing with this as of now.

Tvis

Take a look here:
http://dynamicsusers.net/forums/t/17457.aspx

And here:
http://groups.google.com/group/microsof ... e0f42bac33