<div id="docs-body">
 <div class="chapter">
  <div class="titlepage">
   <div>
    <div>
     <h1 class="title">
      <a name="functions">
      </a>
      Chapter 14 Functions and Operators
     </h1>
    </div>
   </div>
  </div>
  <div class="toc">
   <p>
    <b>
     Table of Contents
    </b>
   </p>
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="built-in-function-reference.html">
       14.1 Built-In Function and Operator Reference
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="loadable-function-reference.html">
       14.2 Loadable Function Reference
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="type-conversion.html">
       14.3 Type Conversion in Expression Evaluation
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="non-typed-operators.html">
       14.4 Operators
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="operator-precedence.html">
         14.4.1 Operator Precedence
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="comparison-operators.html">
         14.4.2 Comparison Functions and Operators
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="logical-operators.html">
         14.4.3 Logical Operators
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="assignment-operators.html">
         14.4.4 Assignment Operators
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="flow-control-functions.html">
       14.5 Flow Control Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="numeric-functions.html">
       14.6 Numeric Functions and Operators
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="arithmetic-functions.html">
         14.6.1 Arithmetic Operators
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="mathematical-functions.html">
         14.6.2 Mathematical Functions
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="date-and-time-functions.html">
       14.7 Date and Time Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="string-functions.html">
       14.8 String Functions and Operators
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="string-comparison-functions.html">
         14.8.1 String Comparison Functions and Operators
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="regexp.html">
         14.8.2 Regular Expressions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="string-functions-charset.html">
         14.8.3 Character Set and Collation of Function Results
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="fulltext-search.html">
       14.9 Full-Text Search Functions
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="fulltext-natural-language.html">
         14.9.1 Natural Language Full-Text Searches
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="fulltext-boolean.html">
         14.9.2 Boolean Full-Text Searches
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="fulltext-query-expansion.html">
         14.9.3 Full-Text Searches with Query Expansion
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="fulltext-stopwords.html">
         14.9.4 Full-Text Stopwords
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="fulltext-restrictions.html">
         14.9.5 Full-Text Restrictions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="fulltext-fine-tuning.html">
         14.9.6 Fine-Tuning MySQL Full-Text Search
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="full-text-adding-collation.html">
         14.9.7 Adding a User-Defined Collation for Full-Text Indexing
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="fulltext-search-ngram.html">
         14.9.8 ngram Full-Text Parser
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="fulltext-search-mecab.html">
         14.9.9 MeCab Full-Text Parser Plugin
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="cast-functions.html">
       14.10 Cast Functions and Operators
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="xml-functions.html">
       14.11 XML Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="bit-functions.html">
       14.12 Bit Functions and Operators
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="encryption-functions.html">
       14.13 Encryption and Compression Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="locking-functions.html">
       14.14 Locking Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="information-functions.html">
       14.15 Information Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="spatial-analysis-functions.html">
       14.16 Spatial Analysis Functions
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="spatial-function-reference.html">
         14.16.1 Spatial Function Reference
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-function-argument-handling.html">
         14.16.2 Argument Handling by Spatial Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="gis-wkt-functions.html">
         14.16.3 Functions That Create Geometry Values from WKT Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="gis-wkb-functions.html">
         14.16.4 Functions That Create Geometry Values from WKB Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="gis-mysql-specific-functions.html">
         14.16.5 MySQL-Specific Functions That Create Geometry Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="gis-format-conversion-functions.html">
         14.16.6 Geometry Format Conversion Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="gis-property-functions.html">
         14.16.7 Geometry Property Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-operator-functions.html">
         14.16.8 Spatial Operator Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-relation-functions.html">
         14.16.9 Functions That Test Spatial Relations Between Geometry Objects
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-geohash-functions.html">
         14.16.10 Spatial Geohash Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-geojson-functions.html">
         14.16.11 Spatial GeoJSON Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-aggregate-functions.html">
         14.16.12 Spatial Aggregate Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-convenience-functions.html">
         14.16.13 Spatial Convenience Functions
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="json-functions.html">
       14.17 JSON Functions
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="json-function-reference.html">
         14.17.1 JSON Function Reference
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="json-creation-functions.html">
         14.17.2 Functions That Create JSON Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="json-search-functions.html">
         14.17.3 Functions That Search JSON Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="json-modification-functions.html">
         14.17.4 Functions That Modify JSON Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="json-attribute-functions.html">
         14.17.5 Functions That Return JSON Value Attributes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="json-table-functions.html">
         14.17.6 JSON Table Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="json-validation-functions.html">
         14.17.7 JSON Schema Validation Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="json-utility-functions.html">
         14.17.8 JSON Utility Functions
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="replication-functions.html">
       14.18 Replication Functions
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="group-replication-functions.html">
         14.18.1 Group Replication Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="gtid-functions.html">
         14.18.2 Functions Used with Global Transaction Identifiers (GTIDs)
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="replication-functions-async-failover.html">
         14.18.3 Asynchronous Replication Channel Failover Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="replication-functions-synchronization.html">
         14.18.4 Position-Based Synchronization Functions
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="aggregate-functions-and-modifiers.html">
       14.19 Aggregate Functions
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="aggregate-functions.html">
         14.19.1 Aggregate Function Descriptions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="group-by-modifiers.html">
         14.19.2 GROUP BY Modifiers
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="group-by-handling.html">
         14.19.3 MySQL Handling of GROUP BY
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="group-by-functional-dependence.html">
         14.19.4 Detection of Functional Dependence
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="window-functions.html">
       14.20 Window Functions
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="window-function-descriptions.html">
         14.20.1 Window Function Descriptions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="window-functions-usage.html">
         14.20.2 Window Function Concepts and Syntax
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="window-functions-frames.html">
         14.20.3 Window Function Frame Specification
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="window-functions-named-windows.html">
         14.20.4 Named Windows
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="window-function-restrictions.html">
         14.20.5 Window Function Restrictions
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="performance-schema-functions.html">
       14.21 Performance Schema Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="internal-functions.html">
       14.22 Internal Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="miscellaneous-functions.html">
       14.23 Miscellaneous Functions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="precision-math.html">
       14.24 Precision Math
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="precision-math-numbers.html">
         14.24.1 Types of Numeric Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="precision-math-decimal-characteristics.html">
         14.24.2 DECIMAL Data Type Characteristics
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="precision-math-expressions.html">
         14.24.3 Expression Handling
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="precision-math-rounding.html">
         14.24.4 Rounding Behavior
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="precision-math-examples.html">
         14.24.5 Precision Math Examples
        </a>
       </span>
      </dt>
     </dl>
    </dd>
   </dl>
  </div>
  <a class="indexterm" name="idm46045210294496">
  </a>
  <a class="indexterm" name="idm46045210292992">
  </a>
  <a class="indexterm" name="idm46045210291920">
  </a>
  <p>
   Expressions can be used at several points in
   <a class="link" href="glossary.html#glos_sql" title="SQL">
    SQL
   </a>
   statements, such as in the
   <code class="literal">
    ORDER BY
   </code>
   or
   <code class="literal">
    HAVING
   </code>
   clauses of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements, in the
   <code class="literal">
    WHERE
   </code>
   clause of a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   ,
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   , or
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statement, or in
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   statements. Expressions can be written using values from several
    sources, such as literal values, column values,
   <code class="literal">
    NULL
   </code>
   , variables, built-in functions and
    operators, loadable functions, and stored functions (a type of
    stored object).
  </p>
  <p>
   This chapter describes the built-in functions and operators that are
    permitted for writing expressions in MySQL. For information about
    loadable functions and stored functions, see
   <a class="xref" href="server-loadable-functions.html" title="7.7 MySQL Server Loadable Functions">
    Section 7.7, “MySQL Server Loadable Functions”
   </a>
   , and
   <a class="xref" href="stored-routines.html" title="27.2 Using Stored Routines">
    Section 27.2, “Using Stored Routines”
   </a>
   . For the rules describing how the
    server interprets references to different kinds of functions, see
   <a class="xref" href="function-resolution.html" title="11.2.5 Function Name Parsing and Resolution">
    Section 11.2.5, “Function Name Parsing and Resolution”
   </a>
   .
  </p>
  <p>
   An expression that contains
   <code class="literal">
    NULL
   </code>
   always produces
    a
   <code class="literal">
    NULL
   </code>
   value unless otherwise indicated in the
    documentation for a particular function or operator.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    By default, there must be no whitespace between a function name
      and the parenthesis following it. This helps the MySQL parser
      distinguish between function calls and references to tables or
      columns that happen to have the same name as a function. However,
      spaces around function arguments are permitted.
   </p>
   <p>
    To tell the MySQL server to accept spaces after function names by
      starting it with the
    <a class="link" href="server-options.html#option_mysqld_sql-mode">
     <code class="option">
      --sql-mode=IGNORE_SPACE
     </code>
    </a>
    option.
      (See
    <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
     Section 7.1.11, “Server SQL Modes”
    </a>
    .) Individual client programs can
      request this behavior by using the
    <code class="literal">
     CLIENT_IGNORE_SPACE
    </code>
    option for
    <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-connect.html" target="_top">
     <code class="literal">
      mysql_real_connect()
     </code>
    </a>
    . In either
      case, all function names become reserved words.
   </p>
  </div>
  <p>
   For the sake of brevity, some examples in this chapter display the
    output from the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   program in abbreviated
    form. Rather than showing examples in this format:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5947103"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">MOD</span><span class="token punctuation">(</span><span class="token number">29</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> mod(29,9) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>         2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   This format is used instead:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53004479"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">MOD</span><span class="token punctuation">(</span><span class="token number">29</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">2</span></code></pre>
  </div>
 </div>
 <br/>
</div>
