Re[2]: [GENERAL] case-insensitive like operator - Mailing list pgsql-general

Hello David,

Wednesday, January 12, 2000, 4:48:00 PM, you wrote:

DW> Yura,

DW> As you have no control over the queries generated by MS Access I guess
DW> you need to create extra columns which hold the uppercase version of the
DW> data. Then to search use these columns and uppercase the data you are
DW> searching for

DW> eg if you have a column name then add a new column name_upper, change
DW> your data entry to always put upper(name) into name_upper

DW> Your search should be where name_upper like "UPPERCASED VALUE"

DW> Dave

DW> yura wrote:
>>
>> Hello All,
>>
>> I have a following problem:
>>
>> We have ported MS Acess database to PostgreSQL. Everything ok, but our
>> user are used to search data in tables using filters, and Access does
>> case insensitive search, but when working with Postgres database it
>> converts filters into queries with 'like' operator. So is there any
>> way to make 'like' operator case insensitive? Or maybe somebody has
>> the same problems and knows the solution?
>>
>> Best regards,
>>  Yury                          mailto:yura@vpcit.ru
>>
>> ************

DW> ************

Thanks for all replies, I have found the solution.
I have redefined the operator '~~' (it's the same as 'like') so now it
compares strings case insentively.

drop function ictextlike(text, text);
create function ictextlike(text, text)
  returns bool
  as '
  begin
    if textlike(upper($1), upper($2)) then
      return TRUE;
    else
      return FALSE;
    end if;
  end;
  '
  language 'plpgsql';

drop operator ~~ (text, text);
create operator ~~ (
        leftarg=text,
        rightarg=text,
        procedure=ictextlike,
        negator='!~~'
        );


Best regards,
 yura                            mailto:yura@vpcit.ru