Case sensitive order by

Hi,

I want to do case sensitve sorting on a varchar field.

the default ordering is case sensitive , what makes u feel
otherwise , could u give some examples?

That is I will have strings in the varchar field which will have
character from ascii(32) to ascii(255). I want sort result by ascii values.

Deepak

---------------------------(end of broadcast)--------------------------- TIP 5: Have you
checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

On Fri, Apr 11, 2003 at 23:49:12 +0530,
mallah@trade-india.com wrote:

I want to do case sensitve sorting on a varchar field.

the default ordering is case sensitive , what makes u feel
otherwise , could u give some examples?

That depends on the locale. In US_EN ordering is not case sensitive.
(Unless to strings are the same excepting their case.)

Deepak Joglekar <deepak.joglekar@nebula-soft.com> writes:

I want to do case sensitve sorting on a varchar field.

That is I will have strings in the varchar field which will have
character from ascii(32) to ascii(255). I want sort result by ascii
values.

If you want a straight-ASCII sort without any funny locale sort rules,
you need to be careful to initdb in C locale.

regards, tom lane

Is there any tech docs or manual pages discussing using UTF-8 in Postgres?

Tom Lane wrote:

On Fri, Apr 11, 2003 at 03:15:41PM -0500, Bruno Wolff III wrote:

On Fri, Apr 11, 2003 at 23:49:12 +0530,
mallah@trade-india.com wrote:

I want to do case sensitve sorting on a varchar field.

the default ordering is case sensitive , what makes u feel
otherwise , could u give some examples?

That depends on the locale. In US_EN ordering is not case sensitive.
(Unless to strings are the same excepting their case.)

Stupid question: would US_EN also result in where clauses being
case-insensitive? I'd like that behavior, but am currently doing it
using where lower(blah) = lower('search phrase')
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Sat, Apr 12, 2003 at 13:08:03 -0500,
"Jim C. Nasby" <jim@nasby.net> wrote:

On Fri, Apr 11, 2003 at 03:15:41PM -0500, Bruno Wolff III wrote:

On Fri, Apr 11, 2003 at 23:49:12 +0530,
mallah@trade-india.com wrote:

I want to do case sensitve sorting on a varchar field.

the default ordering is case sensitive , what makes u feel
otherwise , could u give some examples?

That depends on the locale. In US_EN ordering is not case sensitive.
(Unless to strings are the same excepting their case.)

Stupid question: would US_EN also result in where clauses being
case-insensitive? I'd like that behavior, but am currently doing it
using where lower(blah) = lower('search phrase')

No.

P.S.
I double checked the name of the local and it is really 'en_US'.

Hi,

On Sat, 2003-04-12 at 02:24, Tom Lane wrote:

Deepak Joglekar <deepak.joglekar@nebula-soft.com> writes:

I want to do case sensitve sorting on a varchar field.

That is I will have strings in the varchar field which will have
character from ascii(32) to ascii(255). I want sort result by ascii
values.

If you want a straight-ASCII sort without any funny locale sort rules,
you need to be careful to initdb in C locale.

initdb -D /path-to-data --locale=C
It works the way I want ( pure ascii sort ). Thanks a lot.

I want to know is there any way to choose what type of sort to use
during query. i.e sort based on EN_US, or C.

Best regards.

Deepak Joglekar

Jim C. Nasby wrote:

On Fri, Apr 11, 2003 at 03:15:41PM -0500, Bruno Wolff III wrote:

On Fri, Apr 11, 2003 at 23:49:12 +0530,
mallah@trade-india.com wrote:

I want to do case sensitve sorting on a varchar field.

the default ordering is case sensitive , what makes u feel
otherwise , could u give some examples?

That depends on the locale. In US_EN ordering is not case sensitive.
(Unless to strings are the same excepting their case.)

Stupid question: would US_EN also result in where clauses being
case-insensitive? I'd like that behavior, but am currently doing it
using where lower(blah) = lower('search phrase')

These are some functions that can do what you want I think:

TEXT Insensitive Case LIKE
boolean texticlike(text, text)
~~*

TEXT Insensitive Case Not LIKE
boolean texticnlike(text, text)
!~~*

TEXT Insensitive Case REGEX EQual
boolean texticregexeq(text, text)
~*

TEXT Insensitive Case REGEX Not EQual
boolean texticregexne(text, text)
!~*

Use them like this:

... where texticlike(blah,'search phrase') ...

or

... where blah ~~* 'search phrase' ...

I hope that helps.

Guy

On Tue, Apr 15, 2003 at 12:03:13PM -0600, Guy Fraser wrote:

Use them like this:

... where texticlike(blah,'search phrase') ...

or

... where blah ~~* 'search phrase' ...

How well will this utilize indexes? With the WHERE lower(field) =
lower('search') method, I can index on lower(field), so it's still fast.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"