SQLAlchemy filter, substr, case
python, sqlalchemy
Solution
You do not need to use a `CASE()` statement here. Just use Python:
query.filter(func.substr(value, 1 if len(value) > 7 else 1, 7) == '1050430')
This is called a conditional expression; it's constructed as `true_expr if test_expr else false_expr`, where `true_expr` is used if the `test_expr` evaluates to True, otherwise the `false_expr` part is used.
If `value` is not a constant but a column reference, then you do need to use a `CASE()` statement. Use `sqlalchemy.sql.expression.case()` for that:
from sqlalchemy.sql.expression import case
query.filter(func.substr(value, case([(func.length(column) > 7, func.length(column) - 7 + 1)], else_=1), 7) == '1050430')
but you need to make sure your `(` and `)` parenthesis are balanced (you had one too many).
Problem
Can I use a `CASE` statement within `substr` and `filter`? Here is my requirement: ``` >> value = '00021050430' #here value is a database column >> query.filter((func.substr(value,case([(func.length(value) > 7,func.length(varying_value)-7+1))],else_=1),7)=='1050430') ``` Output I am expecting is: ``` >> query.filter(func.substr(value,6,7)) ``` The above throws an error.