select the sort order in sybase

Selecting the Sort Order

Different languages sort the same characters differently. SAP ASE uses sort orders to create indexes, store date into indexed tables, and specify an order by clause.

For example, in English, Cho is sorted before Co, whereas in Spanish, the opposite is true. In German, β is a single character, however in dictionaries it is treated as the double character ss and sorted accordingly. Accented characters are sorted in a particular order so that aménité comes before amène, whereas if you ignored the accents, the reverse would be true. Therefore, language-specific sort orders are required so that characters are sorted correctly.

Each character set comes with one or more sort orders that SAP ASE uses to collate data. A sort order is tied to a particular language or set of languages and to a specific character set. The same sort orders can be used for English, French, and German because they sort the same characters identically, for example, A, a, B, b, and so on. Or the characters are specific to one of the languages—for example, the accented characters, é , à, and á, are used in French but not in English or German—and therefore, there is no conflict in how those characters are sorted. The same is not true for Spanish however, where the double letters ch and ll are sorted differently. Therefore, although the same character sets support all four languages, there is one set of sort orders for English, French and German, and a different set of sort orders for Spanish.

In addition, a sort order is tied to a particular character set. Therefore, there is one set of sort orders for English, French, and German in the ISO 8859-1 character set, another set in the CP 850 character set, and so on. The sort orders available for a particular character set are located in sort order definition files (*.srt files) in the character set directory.

• Different Types of Sort Orders
  All character sets are offered with a binary sort order at a minimum, which blindly sorts all data based only on the arithmetic value of the code assigned to represent each letter (the “binary” code) in the character set.
• Selecting the Default Sort Order
  SAP servers can support only one default sort order at a time. If your users all use the same language, or their languages all use the same sort order, selecting the default sort order is straightforward.

1860413 - How to change character set or sort order of SAP ASE

Symptom

• How to change the default character set and sort order of ASE?
• Steps to change character set or sort order in ASE.

Environment

SAP Adaptive Server Enterprise (ASE)

Resolution

Below 2 examples of how to change character set and sort order.

EXAMPLE 1 Change from iso_1 character set to UTF8 character set, keeping binary sort order=============================================================================

1. Run sp_helpsort to check current character set & sort order (output truncated for clarity) :

sp_helpsort
go

Sort Order Description

Character Set = 1, iso_1ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 50, bin_iso_1Binary ordering, for the ISO 8859/1 or Latin-1 character set (iso_1).

2. Check which character sets and sort orders are installed:

select type, id, csid, name from syscharsets
go

Note: For 'type' columns, numbers from 1001 to 1999 represent character sets. Numbers from 2000 to 2999 represent sort orders.

3. Add a UTF8 charset with binary sort order to ASE, using 'charset' utility which can be found in $SYBASE/ASE-1*_0/bin:

charset -Usa -SASE binary.srt utf8

.....
Finished loading file 'binary.srt'.
1 sort order loaded successfully

4. Change default character set:

sp_configure 'default character set id', 190   --  character set # 190 is utf8
go

5. Restart ASE twice.

On first restart, ASE will detect character set change and will shutdown automatically after these log messages :

Default Sort Order successfully changed.
ASE shutdown after verifying System Indexes.

6. Run sp_helpsort to check changed character set:

sp_helpsort
go

Sort Order Description

Character Set = 190, utf8
Unicode 3.1 UTF-8 Character Set
Class 2 Character Set
Sort Order = 50, bin_utf8
Binary sort order for the ISO 10646-1, UTF-8 multibyte encoding character set (utf8).

         

EXAMPLE 2 Change from iso_1 with binary sort order to cp1250 with noaccents sort order==========================================================================

The second example is similar to the first. Please refer to Example 1 for explanations / more detailed outputs.

1. Run sp_helpsort to check current character set & sort order (output truncated for clarity) :

sp_helpsort
go

2. Check which character sets and sort orders are installed:

select type, id, csid, name from syscharsets
go

3. Add the cp1250 charset with binary AND noaccent sort orders to ASE, using 'charset' utility:
   

charset -Usa -SASE binary.srt cp1250
charset -Usa -SASE noaccents.srt cp1250

4. Change default character set:

sp_configure "default character set id", 22  --  character set # 22 is cp1250
go
sp_configure "default sortorder id", 54       --  sort order id # 54 is noaccents
go

5. Restart ASE twice.

6. Run sp_helpsort to check that the new character set has been changed successfully.

sp_helpsort
go

Sort Order Description

Character Set = 22, cp1250
Microsoft Windows Code Page 1250, Eastern Europe
Sort Order = 54, noaccents_cp1250
Polish Windows dictionary sort order. Uses the Polish and EE Windows code page 1250 character set and is case and accent insensitive.

7. Run dbcc reindex when needed, see KBA 2496376 - Index is marked as suspect after sort order has been changed. SAP ASE