Re[2]: [GENERAL] case-insensitive like operator

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(at)vpcit(dot)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(at)vpcit(dot)ru