Re: Case sensitive order by

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