Case insensitive pattern matching search

>> I'm trying to construct a case-insensitive pattern matching search which
>> should allow the wild card character, i.e. % and _. The 'like' predicate
>> only provides the case SENSITIVE capability. And    lowercase(colname)
>> like pattern does not work since it is an erron in syntax. Any ideas and
>> hints are greatly appreciated!
>> 
>> J.Hu
>> jianhu@bbsrc.ac.uk

>Hello, 
> 
>   I have seen this question pop up several times in the past couple of 
>months but I've been reluctant to post my solution to this problem 
>because it utilizes a C function and therefore does not address the 
>problem for the report writer and isql.  However since this is a recurring 
>issue, I might as well put in my $.02 worth.

>   The problem is ingres 6.4/* does not allow the following:

>   select *
>      from bar
>      where lowercase(colname) like lowercase(foo); 

>   If one is trying to do a pattern match for the string 'Terry Hawkins' 
>within some text and the table contains the equivalent string (at least in 
>this context) 'terry hawkins', without being able to fold the characters 
>up|down in conjunction with the like predicate a generalized solution is very 
>difficult to obtain with SQL.
>   Since ingres does allow specific characters to be matched by placing 
>them in []'s, one approach to the above query would be to do something like 
>this:

>   select * 
>      from bar
>      where colname like '%\[Tt\]\[Ee\]\[Rr\]\[Rr\]\[Yy\]\[ \]
>         \[Hh\]\[Aa\]\[Ww\]\[Kk\]\[Ii\]\[Nn\]\[Ss\]%' escape '\';
>   
>   This allows the string to be matched to either upper or lower 
>case characters.  Of course programs are rarely written for specific cases 
>like this one and no users in their right minds would allow a programmer 
>to impose this type of query on them.  
>   What is needed is a function that will take the original string and 
>expand it to the pattern needed for the query.  The major advantage is 
>that it makes this type of query simple to perform.  The disadvantage is, as 
>I mentioned earlier, it can only be called from a program.
>   I've written a small C function to do what I just described.  If 
>anyone is interested in seeing the code, I'll be happy to post it.


>/*
>Terry J. Hawkins                    thawkins@icarus.sfbr.org
>SW Foundation for Biomed. Res.      Phone: (210) 674-1410 x417
>Scientific Computing                Fax: (210) 670-3322
>*/


One solution is to have a view on the table.  The target field in the
base table would be extracted as either lowercase(target_field) or
uppercase(target_field) into the view.  Then, the search can be 
performed on the view.

==============================================================================
Nabil Courdy               |
York & Associates, Inc.    |
Minneapolis, MN            |  ncourdy@winternet.com
(612)831-0077              |
(612)831-0887 (FAX)        |
==============================================================================

Jian Hu (jianhu@bbsrc.ac.uk) wrote:
: I'm trying to construct a case-insensitive pattern matching search
: which should allow the wild card character, i.e. % and _. The 'like'
: predicate only provides the case SENSITIVE capability. And
: lowercase(colname) like pattern doest not work since it is an
: erron in syntax. Any ideas and hints are greatly appreciated!

Creating a view that converts the desired column to lowercase and
then select from the view using the like clause will work, but can
be a pain in some situations. If you want to do this often, it may
be worth creating another column that is maintained as always lower
case, and create an index on it - this is what we do for surnames,
streetnames, etc.

I personally have found no other way to do this for the general case.
Specific cases may be possible using the escape keyword as follows:

        WHERE firstname LIKE '\[Aa\]%' escape '\'
        finds Albert, albert, ALFRED, ANtHONY, etc.

or if you are keen:

        WHERE surname LIKE '\[Bb\]\[Rr\]\[Oo\]\[Ww\]\[Nn\]%' escape '\'
        finds Brown, Browning, BROWNLEE, BroWnsdon, etc.

I'm 95% sure that Ingres will not use an index on a column to
satisfy searches like these (same as using LIKE '%...'), but
I stand to be corrected.

I believe the escape keyword is part of the SQL standard but I am
not sure about the above use of [] characters (it works for Ingres).
If you need to know I can check, its just 600+ pages worth of hard
to read stuff (I don't write them, I just look after the computers
of those that do).

I don't know if LOWERCASE/UPPERCASE and LIKE are meant to work
together according to the standard. Maybe if they are, then the next
release of OpenIngres will have it?

Hope this helps,
---------------------------------------------------------------------
Brett Bailie                        Phone: +61 2 746 4859
MIS                                 Fax:   +61 2 746 8450
Standards Australia                 Email: bailie@saa.sa.telememo.au
     No opinions expressed are necessarily shared by my employer
---------------------------------------------------------------------
Ingres Q & A
To William's Home Page
Email William