Thread: Case sensitive order by
Hi, 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. Deepak
> 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: > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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?"