<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.5. Binary String Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-string.html" title="9.4. String Functions and Operators" /><link rel="next" href="functions-bitstring.html" title="9.6. Bit String Functions and Operators" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.5. Binary String Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-string.html" title="9.4. String Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.7 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-bitstring.html" title="9.6. Bit String Functions and Operators">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-BINARYSTRING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.5. Binary String Functions and Operators</h2></div></div></div><a id="id-1.5.8.10.2" class="indexterm"></a><p> This section describes functions and operators for examining and manipulating values of type <code class="type">bytea</code>. </p><p> <acronym class="acronym">SQL</acronym> defines some string functions that use key words, rather than commas, to separate arguments. Details are in <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL" title="Table 9.11. SQL Binary String Functions and Operators">Table 9.11</a>. <span class="productname">PostgreSQL</span> also provides versions of these functions that use the regular function invocation syntax (see <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" title="Table 9.12. Other Binary String Functions">Table 9.12</a>). </p><div class="note"><h3 class="title">Note</h3><p> The sample results shown on this page assume that the server parameter <a class="link" href="runtime-config-client.html#GUC-BYTEA-OUTPUT"><code class="varname">bytea_output</code></a> is set to <code class="literal">escape</code> (the traditional PostgreSQL format). </p></div><div class="table" id="FUNCTIONS-BINARYSTRING-SQL"><p class="title"><strong>Table 9.11. <acronym class="acronym">SQL</acronym> Binary String Functions and Operators</strong></p><div class="table-contents"><table class="table" summary="SQL Binary String Functions and Operators" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td><code class="literal"><em class="parameter"><code>string</code></em> <code class="literal">||</code> <em class="parameter"><code>string</code></em></code></td><td> <code class="type">bytea</code> </td><td> String concatenation <a id="id-1.5.8.10.6.2.2.1.3.1" class="indexterm"></a> </td><td><code class="literal">'\\Post'::bytea || '\047gres\000'::bytea</code></td><td><code class="literal">\\Post'gres\000</code></td></tr><tr><td> <a id="id-1.5.8.10.6.2.2.2.1.1" class="indexterm"></a> <code class="literal"><code class="function">octet_length(<em class="parameter"><code>string</code></em>)</code></code> </td><td><code class="type">int</code></td><td>Number of bytes in binary string</td><td><code class="literal">octet_length('jo\000se'::bytea)</code></td><td><code class="literal">5</code></td></tr><tr><td> <a id="id-1.5.8.10.6.2.2.3.1.1" class="indexterm"></a> <code class="literal"><code class="function">overlay(<em class="parameter"><code>string</code></em> placing <em class="parameter"><code>string</code></em> from <code class="type">int</code> [<span class="optional">for <code class="type">int</code></span>])</code></code> </td><td><code class="type">bytea</code></td><td> Replace substring </td><td><code class="literal">overlay('Th\000omas'::bytea placing '\002\003'::bytea from 2 for 3)</code></td><td><code class="literal">T\\002\\003mas</code></td></tr><tr><td> <a id="id-1.5.8.10.6.2.2.4.1.1" class="indexterm"></a> <code class="literal"><code class="function">position(<em class="parameter"><code>substring</code></em> in <em class="parameter"><code>string</code></em>)</code></code> </td><td><code class="type">int</code></td><td>Location of specified substring</td><td><code class="literal">position('\000om'::bytea in 'Th\000omas'::bytea)</code></td><td><code class="literal">3</code></td></tr><tr><td> <a id="id-1.5.8.10.6.2.2.5.1.1" class="indexterm"></a> <code class="literal"><code class="function">substring(<em class="parameter"><code>string</code></em> [<span class="optional">from <code class="type">int</code></span>] [<span class="optional">for <code class="type">int</code></span>])</code></code> </td><td><code class="type">bytea</code></td><td> Extract substring </td><td><code class="literal">substring('Th\000omas'::bytea from 2 for 3)</code></td><td><code class="literal">h\000o</code></td></tr><tr><td> <a id="id-1.5.8.10.6.2.2.6.1.1" class="indexterm"></a> <code class="literal"><code class="function">trim([<span class="optional">both</span>] <em class="parameter"><code>bytes</code></em> from <em class="parameter"><code>string</code></em>)</code></code> </td><td><code class="type">bytea</code></td><td> Remove the longest string containing only bytes appearing in <em class="parameter"><code>bytes</code></em> from the start and end of <em class="parameter"><code>string</code></em> </td><td><code class="literal">trim('\000\001'::bytea from '\000Tom\001'::bytea)</code></td><td><code class="literal">Tom</code></td></tr></tbody></table></div></div><br class="table-break" /><p> Additional binary string manipulation functions are available and are listed in <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" title="Table 9.12. Other Binary String Functions">Table 9.12</a>. Some of them are used internally to implement the <acronym class="acronym">SQL</acronym>-standard string functions listed in <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL" title="Table 9.11. SQL Binary String Functions and Operators">Table 9.11</a>. </p><div class="table" id="FUNCTIONS-BINARYSTRING-OTHER"><p class="title"><strong>Table 9.12. Other Binary String Functions</strong></p><div class="table-contents"><table class="table" summary="Other Binary String Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td> <a id="id-1.5.8.10.8.2.2.1.1.1" class="indexterm"></a> <code class="literal"><code class="function">btrim(<em class="parameter"><code>string</code></em> <code class="type">bytea</code>, <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>)</code></code> </td><td><code class="type">bytea</code></td><td> Remove the longest string containing only bytes appearing in <em class="parameter"><code>bytes</code></em> from the start and end of <em class="parameter"><code>string</code></em> </td><td><code class="literal">btrim('\000trim\001'::bytea, '\000\001'::bytea)</code></td><td><code class="literal">trim</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.2.1.1" class="indexterm"></a> <code class="literal"><code class="function">decode(<em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>format</code></em> <code class="type">text</code>)</code></code> </td><td><code class="type">bytea</code></td><td> Decode binary data from textual representation in <em class="parameter"><code>string</code></em>. Options for <em class="parameter"><code>format</code></em> are same as in <code class="function">encode</code>. </td><td><code class="literal">decode('123\000456', 'escape')</code></td><td><code class="literal">123\000456</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.3.1.1" class="indexterm"></a> <code class="literal"><code class="function">encode(<em class="parameter"><code>data</code></em> <code class="type">bytea</code>, <em class="parameter"><code>format</code></em> <code class="type">text</code>)</code></code> </td><td><code class="type">text</code></td><td> Encode binary data into a textual representation. Supported formats are: <code class="literal">base64</code>, <code class="literal">hex</code>, <code class="literal">escape</code>. <code class="literal">escape</code> converts zero bytes and high-bit-set bytes to octal sequences (<code class="literal">\</code><em class="replaceable"><code>nnn</code></em>) and doubles backslashes. </td><td><code class="literal">encode('123\000456'::bytea, 'escape')</code></td><td><code class="literal">123\000456</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.4.1.1" class="indexterm"></a> <code class="literal"><code class="function">get_bit(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>offset</code></em>)</code></code> </td><td><code class="type">int</code></td><td> Extract bit from string </td><td><code class="literal">get_bit('Th\000omas'::bytea, 45)</code></td><td><code class="literal">1</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.5.1.1" class="indexterm"></a> <code class="literal"><code class="function">get_byte(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>offset</code></em>)</code></code> </td><td><code class="type">int</code></td><td> Extract byte from string </td><td><code class="literal">get_byte('Th\000omas'::bytea, 4)</code></td><td><code class="literal">109</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.6.1.1" class="indexterm"></a> <code class="literal"><code class="function">length(<em class="parameter"><code>string</code></em>)</code></code> </td><td><code class="type">int</code></td><td> Length of binary string <a id="id-1.5.8.10.8.2.2.6.3.1" class="indexterm"></a> <a id="id-1.5.8.10.8.2.2.6.3.2" class="indexterm"></a> </td><td><code class="literal">length('jo\000se'::bytea)</code></td><td><code class="literal">5</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.7.1.1" class="indexterm"></a> <code class="literal"><code class="function">md5(<em class="parameter"><code>string</code></em>)</code></code> </td><td><code class="type">text</code></td><td> Calculates the MD5 hash of <em class="parameter"><code>string</code></em>, returning the result in hexadecimal </td><td><code class="literal">md5('Th\000omas'::bytea)</code></td><td><code class="literal">8ab2d3c9689aaf18b4958c334c82d8b1</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.8.1.1" class="indexterm"></a> <code class="literal"><code class="function">set_bit(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>offset</code></em>, <em class="parameter"><code>newvalue</code></em>)</code></code> </td><td><code class="type">bytea</code></td><td> Set bit in string </td><td><code class="literal">set_bit('Th\000omas'::bytea, 45, 0)</code></td><td><code class="literal">Th\000omAs</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.9.1.1" class="indexterm"></a> <code class="literal"><code class="function">set_byte(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>offset</code></em>, <em class="parameter"><code>newvalue</code></em>)</code></code> </td><td><code class="type">bytea</code></td><td> Set byte in string </td><td><code class="literal">set_byte('Th\000omas'::bytea, 4, 64)</code></td><td><code class="literal">Th\000o@as</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.10.1.1" class="indexterm"></a> <code class="literal"><code class="function">sha224(<code class="type">bytea</code>)</code></code> </td><td><code class="type">bytea</code></td><td> SHA-224 hash </td><td><code class="literal">sha224('abc')</code></td><td><code class="literal">\x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.11.1.1" class="indexterm"></a> <code class="literal"><code class="function">sha256(<code class="type">bytea</code>)</code></code> </td><td><code class="type">bytea</code></td><td> SHA-256 hash </td><td><code class="literal">sha256('abc')</code></td><td><code class="literal">\xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.12.1.1" class="indexterm"></a> <code class="literal"><code class="function">sha384(<code class="type">bytea</code>)</code></code> </td><td><code class="type">bytea</code></td><td> SHA-384 hash </td><td><code class="literal">sha384('abc')</code></td><td><code class="literal">\xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7</code></td></tr><tr><td> <a id="id-1.5.8.10.8.2.2.13.1.1" class="indexterm"></a> <code class="literal"><code class="function">sha512(<code class="type">bytea</code>)</code></code> </td><td><code class="type">bytea</code></td><td> SHA-512 hash </td><td><code class="literal">sha512('abc')</code></td><td><code class="literal">\xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f</code></td></tr></tbody></table></div></div><br class="table-break" /><p> <code class="function">get_byte</code> and <code class="function">set_byte</code> number the first byte of a binary string as byte 0. <code class="function">get_bit</code> and <code class="function">set_bit</code> number bits from the right within each byte; for example bit 0 is the least significant bit of the first byte, and bit 15 is the most significant bit of the second byte. </p><p> Note that for historic reasons, the function <code class="function">md5</code> returns a hex-encoded value of type <code class="type">text</code> whereas the SHA-2 functions return type <code class="type">bytea</code>. Use the functions <code class="function">encode</code> and <code class="function">decode</code> to convert between the two, for example <code class="literal">encode(sha256('abc'), 'hex')</code> to get a hex-encoded text representation. </p><p> See also the aggregate function <code class="function">string_agg</code> in <a class="xref" href="functions-aggregate.html" title="9.20. Aggregate Functions">Section 9.20</a> and the large object functions in <a class="xref" href="lo-funcs.html" title="35.4. Server-side Functions">Section 35.4</a>. </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-string.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-bitstring.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.4. String Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.6. Bit String Functions and Operators</td></tr></table></div></body></html>