Expression ********** The expression is string literal that can evaluated to boolean value. For example this is string:: 2+1 = 3 but from view point of evaluating, its value is :class:`true` because in mathematic , number :class:`2` plus number :class:`1` is number :class:`3`, so first expression convert to :class:`3 = 3` and because :class:`3` is equal :class:`3` then overall, expression evaluated :class:`true`. Actually, in a expression, we try to convert symbols to values of symbols and then evaluate result to find out that it is :class:`true` or :class:`false`. Expressions become so Practical when use with valuable. In fact valuables convert expression to dynamic valuable, that can use in many way. without variables, expression is a static or hard coded value. For example in:: A > 40 id variable :class:`A` is more than :class:`40`, then evaluated result is :class:`true` , but if it's value is equal or less than :class:`40` result id :class:`false` Operators ========= When you create comparison expressions, the following operators are allowed:: < > <= >= <> = IN LIKE The following arithmetic operators are also supported in expressions:: + (addition) - (subtraction) * (multiplication) / (division) % (modulus) IN -- Use :class:`IN` For Find out presented value is one of the listed values or no. For example expression:: A IN ( 1 , 4 , 5) Is :class:`true` if variable :class:`A` is :class:`1` or :class:`4` or :class:`5`. In other case result is :class:`false`. LIKE ---- Use this operator for string comparison with :class:`Wildcard Characters`:: expression LIKE pattern expression: :class:`string` The string to be evaluated. pattern :class:`string` pattern to be searched. For example in:: name LIKE 'ali' If value :class:`name` of is :class:`ali` or :class:`ALI` or :class:`aLi`, result is :class:`true`. In fact if :class:`Wildcard Characters` not use in pattern, :class:`LIKE` act like :class:`=` operator. So :class:`name LIKE 'ali'` is equal :class:`name = 'ali'`. For complex pattern :class:`Wildcard Characters` used. For example:: name LIKE '*i' Say that result id :class:`true` only if name value end with :class:`i`. So :class:`ali` or :class:`s.alI` or :class:`i` cause :class:`true` result and :class:`alireza` or :class:`reza` generate :class:`false` result. The :class:`*` or :class:`%` represent any occur of characters. .. note:: string comparisons are case-insensitive. Wildcard Characters ------------------- Both the :class:`*` and :class:`%` can be used interchangeably for wildcard characters in a :class:`LIKE` comparison. If the string in a :class:`LIKE` clause contains a :class:`*` or :class:`%`, those characters should be enclosed in brackets :class:`[]`. If a bracket is in the clause, each bracket character should be enclosed in brackets (for example :class:`[` or :class:`]`). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:: userName LIKE '*ali*' userName LIKE '*ali' userName LIKE 'ali*' Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed. Functions ========= The following functions are also supported: LEN --- Gets the length of a string:: LEN(expression) expression: :class:`string` The string to be evaluated. For example:: Len(userName) > 12 Evaluated :class:`true` if length of string value of variable :class:`userName` ig grater than :class:`12`. ISNULL ------ Checks an expression and either returns the checked expression or a replacement value:: ISNULL(expression, replacementvalue) expression :class:`any` The expression to check. replacementvalue :class:`any` If :class:`expression` is :class:`null`, :class:`replacementvalue` is returned. For example in:: IsNull(price, -1) > 10 If :class:`price` value is :class:`null`, -1 returned form :class:`IsNull` function and evaluated value is :class:`false`. IIF --- Gets one of two values depending on the result of a logical expression:: IIF(expr, truepart, falsepart) expr :class:`any` The expression to evaluate. truepart :class:`any` The value to return if the expression is true. falsepart :class:`any` The value to return if the expression is false. For example:: IIF(total>1000, 'expensive', 'dear') = 'dear' TRIM ---- Removes all leading and trailing blank characters like \r, \n, \t, ' ' :: TRIM(expression) expression :class:`string` The expression to trim. SUBSTRING --------- Gets a sub-string of a specified length, starting at a specified point in the string:: SUBSTRING(expression, start, length) expression :class:`string` The source string for the substring. start :class:`number` Integer that specifies where the substring starts.Base index is :class:`0` length :class:`number` Integer that specifies the length of the substring. For example:: SUBSTRING(phone, 0, 3) = '021' Logical operators ================= For create complex an advanced expression, we can use operators like :class:`AND`, :class:`OR` and :class:`NOT`. with this type of operators we can evaluate based on more than one condition. For example:: A > 40 AND A < 50 In this Example, expression become true if value of :class:`A` become more than :class:`40` and also less than :class:`50`. No other range acceptable. In face two sub-expression or condition :class:`A > 40` and :class:`A < 50` evaluated and then result of them :class:`AND` together for generate final result. In below tables, see output of this operator for deferent inputs: .. csv-table:: AND, OR Operators Sheet :header: "Condition A", "Condition B","OR", "AND" :widths: 10, 10,10, 10 :align: center True, True,True, True True, False,True, False False, False,False, False False, True,True, False .. csv-table:: NOT Operator Sheet :header: "Condition A", "NOT" :widths: 10, 10 :align: center True, False False, True The :class:`NOT` operator use like this:: NOT(A = 3) In above expression for all :class:`A` values except :class:`3`, generate :class:`true` and for :class:`3` result is :class:`false`. .. note:: In expressions, operators or function is case insensitive. For example :class:`AND` or :class:`And` or :class:`and` is equal. .. important:: Because value of variable retrieved from :doc:`[-sharp-sharp-notation` injected to expression , if value has string type you must place variable inside :class:`'` or :class:`"` form prevent syntax error. For example suppose submitted form has :class:`name` filed that has string value :class:`amir`. In :class:`len([##cms.form.name##]) > 4` expression after inject value, expression is :class:`len(amir) > 4`. This expression is invalid because now :class:`amir` recognizing as new variable not string literal value. For fix this problem, change original expression to :class:`len('[##cms.form.name##]') > 4`. Now after injection, expression is :class:`len('amir') > 4` that is valid expression.