Thread: sorting RTL languages.

Hi all,
I need to get sorted data from a database I have on postgresql,
the data is in Hebrew which is written from right to left,
now when I do a SELECT ... ORDER BY ... I get the information sorted by the
last letter first.

Is there anyway to overcome this? other than holding the data reversed?

Many thanks,
    -Amir Hardon.

Amir Hardon <hardon@actcom.co.il> writes:
> I need to get sorted data from a database I have on postgresql,
> the data is in Hebrew which is written from right to left,
> now when I do a SELECT ... ORDER BY ... I get the information sorted by the
> last letter first.
> Is there anyway to overcome this? other than holding the data reversed?

If your machine has a Hebrew locale setting in which sorting is done
right-to-left, then you could initdb in that locale, and text sorting
would happen according to the locale rules.

If you want only some of your columns to be sorted per Hebraic rules,
I'm afraid we have no good answer at present :-( --- database locale
settings are all-or-nothing, at least as far as sort ordering goes.

            regards, tom lane

On Sun, 2 Feb 2003, Tom Lane wrote:

> Amir Hardon <hardon@actcom.co.il> writes:
> > I need to get sorted data from a database I have on postgresql,
> > the data is in Hebrew which is written from right to left,
> > now when I do a SELECT ... ORDER BY ... I get the information sorted by the
> > last letter first.
> > Is there anyway to overcome this? other than holding the data reversed?
>
> If your machine has a Hebrew locale setting in which sorting is done
> right-to-left, then you could initdb in that locale, and text sorting
> would happen according to the locale rules.
>
> If you want only some of your columns to be sorted per Hebraic rules,
> I'm afraid we have no good answer at present :-( --- database locale
> settings are all-or-nothing, at least as far as sort ordering goes.

Presumably it is feasible to create a new rtl text type. Possibly not pretty
though.


--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
>> If you want only some of your columns to be sorted per Hebraic rules,
>> I'm afraid we have no good answer at present :-( --- database locale
>> settings are all-or-nothing, at least as far as sort ordering goes.

> Presumably it is feasible to create a new rtl text type. Possibly not pretty
> though.

Well, if it was *only* a matter of rtl then he could do
    ORDER BY reverse(foo)
I don't think we have a string-reversing function (reverse('foo') = 'oof')
at the moment, but one would be easy to write.

But I'm assuming the rtl property is just one of the ways in which
Hebraic sorting differs from plain ASCII... so he really needs a locale.

            regards, tom lane

At 04:08 PM 2/2/03 -0500, Tom Lane wrote:

>Well, if it was *only* a matter of rtl then he could do
>         ORDER BY reverse(foo)
>I don't think we have a string-reversing function (reverse('foo') = 'oof')
>at the moment, but one would be easy to write.
>
>But I'm assuming the rtl property is just one of the ways in which
>Hebraic sorting differs from plain ASCII... so he really needs a locale.

Or an imaginary function: order by lang_order('hebrew',foo)

Where lang order converts stuff so that in a C locale it's ordered correctly.

Is that sort of thing possible? There'd be a performance penalty, but it
could allow better flexibility.

Link.

I think that the SQL standard should be extended to include:

blah blah blah ORDER::COLLATION BY blah blah blah

    -OR-

blah blah blah ORDER(COLLATION) BY blah blah blah


It would require the engine to have the collation in its system tables. Other collaltions
could be inserted, written. For example, 'US-EN-NA'=>US English, Numeric Alpha ordering. So
any database could order any language based on tables. The local user would not even have to
have the font to display it, if they weren't interested in displaying it.

I will probably write to the SQL standards board someday soon to propose this.


2/2/2003 7:42:53 PM, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:

>At 04:08 PM 2/2/03 -0500, Tom Lane wrote:
>
>>Well, if it was *only* a matter of rtl then he could do
>>         ORDER BY reverse(foo)
>>I don't think we have a string-reversing function (reverse('foo') = 'oof')
>>at the moment, but one would be easy to write.
>>
>>But I'm assuming the rtl property is just one of the ways in which
>>Hebraic sorting differs from plain ASCII... so he really needs a locale.
>
>Or an imaginary function: order by lang_order('hebrew',foo)
>
>Where lang order converts stuff so that in a C locale it's ordered correctly.
>
>Is that sort of thing possible? There'd be a performance penalty, but it
>could allow better flexibility.
>
>Link.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>