Question Re: Create a norwegian database in Technology Q&A

Create a norwegian database

ovehalseth-1 — Thu, 27 May 2021 14:26:49 GMT

We had a database created like this: CREATE DATABASE 'norwegian.db' COLLATION '819NOR' NCHAR COLLATION 'UCA';

But got problems when trying to store characters outside that collation.

So we reloaded the database to: CREATE DATABASE 'norwegian.db' COLLATION 'UTF8BIN' NCHAR COLLATION 'UTF8BIN';

But now we have problem with sorting and comparison. Well sorting we solved with: set option sort_collation='48' or set option sort_collation='UCA(locale=nob)' (not sure if they are equal, or wich to prefere?)

But when comparing: Name like 'Øyv%' or select if 'æ' = lower('Æ') then 'true' else 'false' end if returns false

How do we create a database that will store in UTF8 but sort and compare in norwegian?

Re: Create a norwegian database

Breck_Carter — Thu, 27 May 2021 16:03:56 GMT

You may find someone with the answer...

If not, consider what a collation-confused person such as myself might do: Perform experiments in which exactly one single variable/parameter/option is changed at a time, followed by all the tests that must be passed to certify "Success!".

Experimental design may take longer than the experiments, but care is necessary to avoid backtracking, wandering, frustration and the inevitable production bugs.

Some parameters to consider are ENCODING and Collation Tailoring Options.

There is a lot of material in the V17 Help here https://help.sap.com/viewer/61ecb3d4d8be4baaa07cc4db0ddb5d0a/17.0/en-US/813826126ce210149074e3a77d2e1dce.html

There have been related discussions in this forum and elsewhere so asking Auntie Google my yield fruit (but be careful of obsolete information).

...and people who know the answer do exist, it's just a matter of finding them 🙂

Re: Create a norwegian database

VolkerBarth — Fri, 28 May 2021 03:03:42 GMT

Just two questions/remarks:

Do you use CHAR or NCHAR data types for those international texts? Because by default NCHAR data will use the UCA collation, which should work fine in your original database. However, if you are using CHAR data types and want to store international data, the default single byte character sets won't allow more than 256 char, obviously. If that limit is critical, you need to use UCA there, as well.
UTF8BIN is a multi-byte character set with binary sorting, so it by design treats each characters with and without accents or case as different. That is apparently not your goal.

If you want multi-byte characters both with CHAR and NCHAR, I guess the following should do:

CREATE DATABASE 'norwegian.db' COLLATION 'UCA(locale=no;case=ignore;accent=ignore)' NCHAR COLLATION 'UCA(locale=no;case=ignore;accent=ignore)';

I can't tell whether the "collation tailoring options" do fit and whether Norwegian has particular sorting variants like German or Swedish (via sorttype=phonebook), so you might adjust that.

Note that using the UCA collation by default uses UTF-8 as encoding.

Just to add: Whether you need to distinguish between Bokmål and Nynorsk is fully out of my scope... 🙂

Re: Create a norwegian database

Former Member — Mon, 31 May 2021 07:41:26 GMT

Hi.

Try to use 1252NOR instead. We always use that in our applications, and it has never created any issues for us.

br,

Bjarne Anker Maritech Systems AS

Re: Create a norwegian database

ovehalseth-1 — Tue, 01 Jun 2021 08:10:35 GMT

Looks promising! Tested: set temporary option sort_collation='UCA(locale=nob;case=ignore;accent=ignore)';

And got correct sorting, so will schedule a conversion of the database and see if it helps 🙂

Oh, and I did not get locale=no to work, but locale=nob did work. I guess it stands for Norwegian Bokmål. But then I would expect that locale=non for Nynorsk should work too, but it did not.

But it does not matter as both sort the same.

Re: Create a norwegian database

VolkerBarth — Tue, 01 Jun 2021 08:24:48 GMT

Note, you can also "test collation sorting" without having to create a new database by using the COMPARE function and specifying the desired collation, see here.

That does also help to compare results of different collation tailoring options side-by-side.