<p dir="auto"><strong>Migrated issue, originally created by v (<a href="https://github.com/vr2262">@vr2262</a>)</strong></p>
<p dir="auto">I would like to filter a query with <code class="notranslate">ILIKE</code> while escaping special characters like <code class="notranslate">%</code> and <code class="notranslate">_</code>. I tried this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class Example(Base):
    __tablename__ = 'example'
    id = Column(Integer, primary_key=True)
    string_col = Column(String)


session = sessionmaker()()
session.add(Example(string_col='S%mething'))

search_string = 's%'
print(
    session.query(Example)
    .filter(
        func.lower(Example.string_col)
        .contains(func.lower(search_string), autoescape=True)
    )
)"><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class Example(Base):
    __tablename__ = 'example'
    id = Column(Integer, primary_key=True)
    string_col = Column(String)


session = sessionmaker()()
session.add(Example(string_col='S%mething'))

search_string = 's%'
print(
    session.query(Example)
    .filter(
        func.lower(Example.string_col)
        .contains(func.lower(search_string), autoescape=True)
    )
)
</code></pre></div>
<p dir="auto">But I get the error <code class="notranslate">TypeError: String value expected when autoescape=True</code> because <code class="notranslate">contains()</code> expects an actual string as the first argument.</p>
<p dir="auto">Ideally SQLAlchemy would provide this feature out of the box, perhaps with <code class="notranslate">icontains()</code> or <code class="notranslate">contains(insensitive=True, ...)</code>.</p>
<p dir="auto"><strong>Migrated issue, originally created by jvanasco (<a href="https://github.com/jvanasco">@jvanasco</a>)</strong></p>
<p dir="auto">discussed previously in</p>
<ul dir="auto">
<li><a href="https://groups.google.com/forum/#!searchin/sqlalchemy/icontains/sqlalchemy/MUyt8zYaP0c/ht4fGR9q9w8J" rel="nofollow">https://groups.google.com/forum/#!searchin/sqlalchemy/icontains/sqlalchemy/MUyt8zYaP0c/ht4fGR9q9w8J</a></li>
<li><a href="https://groups.google.com/forum/#!searchin/sqlalchemy/icontains/sqlalchemy/331XoToT4lk/RPjIYNTkSEIJ" rel="nofollow">https://groups.google.com/forum/#!searchin/sqlalchemy/icontains/sqlalchemy/331XoToT4lk/RPjIYNTkSEIJ</a></li>
</ul>
<p dir="auto">I tried doing this myself, but don't know enough of the internals to get this done on my own.</p>
<p dir="auto">it looks like these need to be modified</p>
<ul dir="auto">
<li>sqlalchemy/sql/default_comparator.py</li>
<li>sqlalchemy/sql/operators.py</li>
</ul>
<p dir="auto">but not sure about</p>
<ul dir="auto">
<li>sqlalchemy/sql/compiler.py</li>
</ul>
<p dir="auto">there are probably more files.</p>
<p dir="auto">if someone can get one working, I can do the rest and build out tests.</p>