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