Thread: Redefining LIKE operator
Hi, I need the LIKE operator to be case insensitive. What I did is to redefine the ~~ so it behaves as the ~~* operator: DROP OPERATOR ~~ (name, text); CREATE OPERATOR ~~ ( leftarg=name, rightarg=text, procedure=nameiclike, negator='!~~'); The operator is created, but when I execute a query, LIKE is still being case-sensitive. Am I doing anything wrong? Is it not possible to redefine this operator? I am using the 7.2.2 version. Thanks in advance Pablo Sajnin psajnin@bejerman.com.ar
--On Friday, March 14, 2003 09:06:29 -0300 Pablo Sajnín <psajnin@bejerman.com.ar> wrote: > Hi, > > I need the LIKE operator to be case insensitive. > What I did is to redefine the ~~ so it behaves as the ~~* operator: > > DROP OPERATOR ~~ (name, text); > CREATE OPERATOR ~~ ( leftarg=name, rightarg=text, procedure=nameiclike, > negator='!~~'); > > > The operator is created, but when I execute a query, LIKE is still being > case-sensitive. > Am I doing anything wrong? > Is it not possible to redefine this operator? > I am using the 7.2.2 version. > What's wrong with ILIKE? > > > Thanks in advance > > > Pablo Sajnin > psajnin@bejerman.com.ar > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
The problem is that I am migrating a system from MS Sql Server (which was installed with the case-insensitive option) and the system will have to still keep working with this RDBMS after the migration, so I have to change the code as less as possible... -----Mensaje original----- De: Larry Rosenman [mailto:ler@lerctr.org] Enviado el: Viernes, 14 de Marzo de 2003 09:16 a.m. Para: Pablo Sajn�n; pgsql-general@postgresql.org Asunto: Re: [GENERAL] Redefining LIKE operator --On Friday, March 14, 2003 09:06:29 -0300 Pablo Sajn�n <psajnin@bejerman.com.ar> wrote: > Hi, > > I need the LIKE operator to be case insensitive. > What I did is to redefine the ~~ so it behaves as the ~~* operator: > > DROP OPERATOR ~~ (name, text); > CREATE OPERATOR ~~ ( leftarg=name, rightarg=text, procedure=nameiclike, > negator='!~~'); > > > The operator is created, but when I execute a query, LIKE is still being > case-sensitive. > Am I doing anything wrong? > Is it not possible to redefine this operator? > I am using the 7.2.2 version. > What's wrong with ILIKE? > > > Thanks in advance > > > Pablo Sajnin > psajnin@bejerman.com.ar > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Pablo Sajn�n <psajnin@bejerman.com.ar> writes:
> I need the LIKE operator to be case insensitive.
> What I did is to redefine the ~~ so it behaves as the ~~* operator:
> DROP OPERATOR ~~ (name, text);
> CREATE OPERATOR ~~ ( leftarg=name, rightarg=text, procedure=nameiclike,
> negator='!~~');
> The operator is created, but when I execute a query, LIKE is still being
> case-sensitive.
There are five operators named '~~'; are you sure your LIKE is invoking
the "name" variant? I'd bet against that, if your tables are declared
with MSSQL-compatible datatypes.
I'd suggest renaming the standard operators out of the way, rather than
dropping them altogether. Also your CREATE is missing the selectivity
estimators. But in general this should work if you do it right.
regards, tom lane