Thread: case-insensitive like operator
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
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?
Use *~ (or ~*, I always mistype it).
Regards,
Joey
--
The MS-DOS filesystem is nice for removable media. -- H. Peter Anvin
Postgres has a load of text search operators. The most powerful is ~* whcih gives you a case insensitive regular expression search. Adriaan
On Sat, 15 Jan 2000, yura wrote: > 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? take a look at ~= and etc. operators, everything you need is there. mazek Marcin Mazurek -- administrator MULTINET SA o/Poznan http://www.multinet.pl/
Hello Adriaan, Wednesday, January 12, 2000, 4:24:53 PM, you wrote: AJ> Postgres has a load of text search operators. The most powerful is ~* AJ> whcih gives you a case insensitive regular expression search. AJ> Adriaan Thank you for reply, but i can't use ~* operator, because i don't create queries, MS Access generates queries and it creates queries with 'like' operator. Best regards, yura mailto:yura@vpcit.ru
Hi Yury, I do not know the most correct solution but I used a work around while encountering a similar problem select * from table_name where upper (field_name) like 'SOME_THING_IN_UPPERCASE' Hope it helps. - Cheers - Shad. + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | Each human owes infinitely more to the human race than to the | | particular country in which he was born. -- Francois Fenelon | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + On Sat, 15 Jan 2000, 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 > > > > ************ >
Hello Shadkam, Wednesday, January 12, 2000, 4:41:20 PM, you wrote: SI> Hi Yury, SI> I do not know the most correct solution but I used a work around while SI> encountering a similar problem SI> select * from table_name SI> where upper (field_name) like 'SOME_THING_IN_UPPERCASE' SI> Hope it helps. SI> - Cheers SI> - Shad. SI> + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + SI> | Each human owes infinitely more to the human race than to the | SI> | particular country in which he was born. -- Francois Fenelon | SI> + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + SI> On Sat, 15 Jan 2000, 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 >> >> >> >> ************ >> Unfortunately, MS Access converts filters into queries select ... from <tab> where <fieldname> like <text> Best regards, yura mailto:yura@vpcit.ru
Yura, As you have no control over the queries generated by MS Access I guess you need to create extra columns which hold the uppercase version of the data. Then to search use these columns and uppercase the data you are searching for eg if you have a column name then add a new column name_upper, change your data entry to always put upper(name) into name_upper Your search should be where name_upper like "UPPERCASED VALUE" Dave 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 > > ************
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
On 2000-01-15, yura mentioned: > Hello Adriaan, > > Wednesday, January 12, 2000, 4:24:53 PM, you wrote: > > AJ> Postgres has a load of text search operators. The most powerful is ~* > AJ> whcih gives you a case insensitive regular expression search. > > AJ> Adriaan > > Thank you for reply, but i can't use ~* operator, because i don't > create queries, MS Access generates queries and it creates queries > with 'like' operator. The way I read the SQL standard, LIKE matches are always case-sensitive, so Access would be in violation. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Best way to handle this IMHO is to write a routine that uses Access
direct ODBC stuff to handle queries that must be case insensitive -
either that or use LCase(fieldname) & LCase(searchvalue) in your
Access queries, but this will be slower.
Here's my routine in VB for the former:
Public Function SQLPass(SQL As String, Optional DontLog As Variant)
Dim wrkODBC As Workspace
Dim conSearch As Connection
Dim qdfTemp As QueryDef
Dim connectstring As String, success As Byte, errornumber As Long
If IsMissing(DontLog) Then DontLog = False
DontLog = True
connectstring = "PUT YOUR CONNECT STRING HERE"
Set wrkODBC = CreateWorkspace("", "moray", "", dbUseODBC)
Set conSearch = wrkODBC.OpenConnection("Search", , ,
connectstring)
Set qdfTemp = conSearch.CreateQueryDef("")
With qdfTemp
.Prepare = dbQUnprepare
.SQL = SQL
success = 1
On Error GoTo SQLerror
.Execute
End With
If Not (DontLog) And (Nz(InStr(SQL, "UPDATE"), 0) > 0 Or
Nz(InStr(SQL, "CREATE"), 0) > 0 Or Nz(InStr(SQL, "INSERT"), 0) > 0 Or
Nz(InStr(SQL, "DELETE"), 0) > 0) Then
LogSQL SQL, success ' log if it is an update or delete or
create or insert query...
End If
conSearch.Close
wrkODBC.Close
Exit Function
SQLerror:
errornumber = Err.number
' Debug.Print errornumber
On Error GoTo 0
If errornumber = 3146 Then
success = 0
MsgBox ("ERROR trying to do " & SQL & " in server database.")
Resume Next
Else
success = 0
If Not (DontLog) = True Then LogSQL SQL, success
Err.Clear
Err.Raise (errornumber)
End If
End Function
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Peter Eisentraut <peter_e@gmx.net>
To: yura <yura@vpcit.ru>
Cc: pgsql-general <pgsql-general@postgreSQL.org>
Sent: Sunday, January 16, 2000 5:13 PM
Subject: Re: Re[2]: [GENERAL] case-insensitive like operator
On 2000-01-15, yura mentioned:
> Hello Adriaan,
>
> Wednesday, January 12, 2000, 4:24:53 PM, you wrote:
>
> AJ> Postgres has a load of text search operators. The most powerful
is ~*
> AJ> whcih gives you a case insensitive regular expression search.
>
> AJ> Adriaan
>
> Thank you for reply, but i can't use ~* operator, because i don't
> create queries, MS Access generates queries and it creates queries
> with 'like' operator.
The way I read the SQL standard, LIKE matches are always
case-sensitive,
so Access would be in violation.
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
************