Skip to content

StringComparisonFilter

Jump to bottom
Mike Bayer edited this page Feb 20, 2014 · 2 revisions

StringComparisonFilter

This example illustrates a simplistic "case insensitive" string type, which applies the LOWER() SQL function to all comparison operations. LOWER() is not in fact a fully featured "case insensitive" system, in that it does not accommodate more sophisticated unicode case folding rules, however the recipe is useful for simple ASCII-oriented situations. It also illustrates how comparison operations can be controlled at the type level.

#!python
from sqlalchemy import *
from sqlalchemy.sql.operators import eq, ne, gt, lt, ge, le, between_op, \
                like_op, notlike_op, contains_op, startswith_op, endswith_op

class LowerCaseString(String):
    case_sensitive = set([eq, ne, gt, lt, ge, le,
                        between_op, like_op, notlike_op,
                        contains_op, startswith_op, endswith_op])
    class Comparator(String.Comparator):

        def operate(self, op, *other, **kw):
            if op in LowerCaseString.case_sensitive:
                other = [func.lower(other) for o in other]
                self = func.lower(self.expr)
                return op(self, *other, **kw)
            else:
                return super(LowerCaseString.Comparator, self).operate(op, *other, **kw)

        def reverse_operate(self, op, other, **kw):
            if op in LowerCaseString.case_sensitive:
                other = func.lower(other)
                self = func.lower(self.expr)
                return op(other, self, **kw)
            else:
                return super(LowerCaseString.Comparator, self).reverse_operate(op, other, **kw)

    comparator_factory = Comparator

t = Table('t', MetaData(), Column('x', LowerCaseString))

for expr in [
    t.c.x == "hi",
    "hi" == t.c.x,
    t.c.x == t.alias().c.x,
    t.c.x + "hi",
    t.c.x.like("hi"),
    t.c.x.contains("hi"),
]:
    print (expr)
Clone this wiki locally