Re: Reverse pattern matching

Gary Greenberg wrote:
> I encountered an unusual problem and wonder if anyone had some similar
> experience. I have a table T where the column contains text values with
> wild cards and a query contain exact value. For example
> ------------------------
> | A | B |
> ------------------------
> |AB% | 1 |
> |X% | 2 |
> |XY% | 3 |
> |% | 4 |
> ------------------------
>
> I'd like to make a query "select B from T where A like 'XYZ'"
> I need to get a result sorted by maximum matching. For the liset case it
> should be:
> 3
> 2
> 4
>
> Any help will be greatly appreciated.

In IBM Informix Dynamic Server (IDS), you'd do:

SELECT b FROM t WHERE 'XYZ' LIKE a;

That is, either side of the LIKE can be an expression as distinct from a column name. The whole point of LIKE is that the operator takes <lhs> LIKE <rhs>, where <rhs> is the pattern, and <lhs> is the string you want to compare with the patter.

I don't know if there's a formal constraint in the SQL standard to prevent this working; there shouldn't be one.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/