]> begriffs open source - ai-pg/blob - full-docs/html/functions-binarystring.html
begriffs.com / projects / ai-pg / blob
summary | shortlog | log | commit | commitdiff | tree
history | raw | HEAD
Include links to all subsection html pages, with shorter paths too
[ai-pg] / full-docs / html / functions-binarystring.html
1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!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 id="docContent" class="container-fluid col-10"><div 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 18.0 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 /></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 <a href="#FUNCTIONS-BINARYSTRING" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.11.2" class="indexterm"></a><p>
3     This section describes functions and operators for examining and
4     manipulating binary strings, that is values of type <code class="type">bytea</code>.
5     Many of these are equivalent, in purpose and syntax, to the
6     text-string functions described in the previous section.
7    </p><p>
8     <acronym class="acronym">SQL</acronym> defines some string functions that use
9     key words, rather than commas, to separate
10     arguments.  Details are in
11     <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL" title="Table 9.11. SQL Binary String Functions and Operators">Table 9.11</a>.
12     <span class="productname">PostgreSQL</span> also provides versions of these functions
13     that use the regular function invocation syntax
14     (see <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" title="Table 9.12. Other Binary String Functions">Table 9.12</a>).
15    </p><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 /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
16         Function/Operator
17        </p>
18        <p>
19         Description
20        </p>
21        <p>
22         Example(s)
23        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
24         <a id="id-1.5.8.11.5.2.2.1.1.1.1" class="indexterm"></a>
25         <code class="type">bytea</code> <code class="literal">||</code> <code class="type">bytea</code>
26         → <code class="returnvalue">bytea</code>
27        </p>
28        <p>
29         Concatenates the two binary strings.
30        </p>
31        <p>
32         <code class="literal">'\x123456'::bytea || '\x789a00bcde'::bytea</code>
33         → <code class="returnvalue">\x123456789a00bcde</code>
34        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
35         <a id="id-1.5.8.11.5.2.2.2.1.1.1" class="indexterm"></a>
36         <code class="function">bit_length</code> ( <code class="type">bytea</code> )
37         → <code class="returnvalue">integer</code>
38        </p>
39        <p>
40         Returns number of bits in the binary string (8
41         times the <code class="function">octet_length</code>).
42        </p>
43        <p>
44         <code class="literal">bit_length('\x123456'::bytea)</code>
45         → <code class="returnvalue">24</code>
46        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
47         <a id="id-1.5.8.11.5.2.2.3.1.1.1" class="indexterm"></a>
48         <code class="function">btrim</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
49         <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
50         → <code class="returnvalue">bytea</code>
51        </p>
52        <p>
53         Removes the longest string containing only bytes appearing in
54         <em class="parameter"><code>bytesremoved</code></em> from the start and end of
55         <em class="parameter"><code>bytes</code></em>.
56        </p>
57        <p>
58         <code class="literal">btrim('\x1234567890'::bytea, '\x9012'::bytea)</code>
59         → <code class="returnvalue">\x345678</code>
60        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
61          <a id="id-1.5.8.11.5.2.2.4.1.1.1" class="indexterm"></a>
62          <code class="function">ltrim</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
63          <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
64          → <code class="returnvalue">bytea</code>
65         </p>
66         <p>
67          Removes the longest string containing only bytes appearing in
68          <em class="parameter"><code>bytesremoved</code></em> from the start of
69          <em class="parameter"><code>bytes</code></em>.
70         </p>
71         <p>
72          <code class="literal">ltrim('\x1234567890'::bytea, '\x9012'::bytea)</code>
73          → <code class="returnvalue">\x34567890</code>
74         </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
75         <a id="id-1.5.8.11.5.2.2.5.1.1.1" class="indexterm"></a>
76         <code class="function">octet_length</code> ( <code class="type">bytea</code> )
77         → <code class="returnvalue">integer</code>
78        </p>
79        <p>
80         Returns number of bytes in the binary string.
81        </p>
82        <p>
83         <code class="literal">octet_length('\x123456'::bytea)</code>
84         → <code class="returnvalue">3</code>
85        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
86         <a id="id-1.5.8.11.5.2.2.6.1.1.1" class="indexterm"></a>
87         <code class="function">overlay</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> <code class="literal">PLACING</code> <em class="parameter"><code>newsubstring</code></em> <code class="type">bytea</code> <code class="literal">FROM</code> <em class="parameter"><code>start</code></em> <code class="type">integer</code> [<span class="optional"> <code class="literal">FOR</code> <em class="parameter"><code>count</code></em> <code class="type">integer</code> </span>] )
88         → <code class="returnvalue">bytea</code>
89        </p>
90        <p>
91         Replaces the substring of <em class="parameter"><code>bytes</code></em> that starts at
92         the <em class="parameter"><code>start</code></em>'th byte and extends
93         for <em class="parameter"><code>count</code></em> bytes
94         with <em class="parameter"><code>newsubstring</code></em>.
95         If <em class="parameter"><code>count</code></em> is omitted, it defaults to the length
96         of <em class="parameter"><code>newsubstring</code></em>.
97        </p>
98        <p>
99         <code class="literal">overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3)</code>
100         → <code class="returnvalue">\x12020390</code>
101        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
102         <a id="id-1.5.8.11.5.2.2.7.1.1.1" class="indexterm"></a>
103         <code class="function">position</code> ( <em class="parameter"><code>substring</code></em> <code class="type">bytea</code> <code class="literal">IN</code> <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> )
104         → <code class="returnvalue">integer</code>
105        </p>
106        <p>
107         Returns first starting index of the specified
108         <em class="parameter"><code>substring</code></em> within
109         <em class="parameter"><code>bytes</code></em>, or zero if it's not present.
110        </p>
111        <p>
112         <code class="literal">position('\x5678'::bytea in '\x1234567890'::bytea)</code>
113         → <code class="returnvalue">3</code>
114        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
115          <a id="id-1.5.8.11.5.2.2.8.1.1.1" class="indexterm"></a>
116          <code class="function">rtrim</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
117          <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
118          → <code class="returnvalue">bytea</code>
119         </p>
120         <p>
121          Removes the longest string containing only bytes appearing in
122          <em class="parameter"><code>bytesremoved</code></em> from the end of
123          <em class="parameter"><code>bytes</code></em>.
124         </p>
125         <p>
126          <code class="literal">rtrim('\x1234567890'::bytea, '\x9012'::bytea)</code>
127          → <code class="returnvalue">\x12345678</code>
128         </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
129         <a id="id-1.5.8.11.5.2.2.9.1.1.1" class="indexterm"></a>
130         <code class="function">substring</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> [<span class="optional"> <code class="literal">FROM</code> <em class="parameter"><code>start</code></em> <code class="type">integer</code> </span>] [<span class="optional"> <code class="literal">FOR</code> <em class="parameter"><code>count</code></em> <code class="type">integer</code> </span>] )
131         → <code class="returnvalue">bytea</code>
132        </p>
133        <p>
134         Extracts the substring of <em class="parameter"><code>bytes</code></em> starting at
135         the <em class="parameter"><code>start</code></em>'th byte if that is specified,
136         and stopping after <em class="parameter"><code>count</code></em> bytes if that is
137         specified.  Provide at least one of <em class="parameter"><code>start</code></em>
138         and <em class="parameter"><code>count</code></em>.
139        </p>
140        <p>
141         <code class="literal">substring('\x1234567890'::bytea from 3 for 2)</code>
142         → <code class="returnvalue">\x5678</code>
143        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
144         <a id="id-1.5.8.11.5.2.2.10.1.1.1" class="indexterm"></a>
145         <code class="function">trim</code> ( [<span class="optional"> <code class="literal">LEADING</code> | <code class="literal">TRAILING</code> | <code class="literal">BOTH</code> </span>]
146         <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> <code class="literal">FROM</code>
147         <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> )
148         → <code class="returnvalue">bytea</code>
149        </p>
150        <p>
151         Removes the longest string containing only bytes appearing in
152         <em class="parameter"><code>bytesremoved</code></em> from the start,
153         end, or both ends (<code class="literal">BOTH</code> is the default)
154         of <em class="parameter"><code>bytes</code></em>.
155        </p>
156        <p>
157         <code class="literal">trim('\x9012'::bytea from '\x1234567890'::bytea)</code>
158         → <code class="returnvalue">\x345678</code>
159        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
160         <code class="function">trim</code> ( [<span class="optional"> <code class="literal">LEADING</code> | <code class="literal">TRAILING</code> | <code class="literal">BOTH</code> </span>] [<span class="optional"> <code class="literal">FROM</code> </span>]
161         <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
162         <em class="parameter"><code>bytesremoved</code></em> <code class="type">bytea</code> )
163         → <code class="returnvalue">bytea</code>
164        </p>
165        <p>
166         This is a non-standard syntax for <code class="function">trim()</code>.
167        </p>
168        <p>
169         <code class="literal">trim(both from '\x1234567890'::bytea, '\x9012'::bytea)</code>
170         → <code class="returnvalue">\x345678</code>
171        </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
172     Additional binary string manipulation functions are available and
173     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
174     of them are used internally to implement the
175     <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>.
176    </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 /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
177         Function
178        </p>
179        <p>
180         Description
181        </p>
182        <p>
183         Example(s)
184        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
185         <a id="id-1.5.8.11.7.2.2.1.1.1.1" class="indexterm"></a>
186         <a id="id-1.5.8.11.7.2.2.1.1.1.2" class="indexterm"></a>
187         <code class="function">bit_count</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code> )
188         → <code class="returnvalue">bigint</code>
189        </p>
190        <p>
191         Returns the number of bits set in the binary string (also known as
192         <span class="quote">“<span class="quote">popcount</span>”</span>).
193        </p>
194        <p>
195         <code class="literal">bit_count('\x1234567890'::bytea)</code>
196         → <code class="returnvalue">15</code>
197        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
198         <a id="id-1.5.8.11.7.2.2.2.1.1.1" class="indexterm"></a>
199         <code class="function">crc32</code> ( <code class="type">bytea</code> )
200         → <code class="returnvalue">bigint</code>
201        </p>
202        <p>
203         Computes the CRC-32 value of the binary string.
204        </p>
205        <p>
206         <code class="literal">crc32('abc'::bytea)</code>
207         → <code class="returnvalue">891568578</code>
208        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
209         <a id="id-1.5.8.11.7.2.2.3.1.1.1" class="indexterm"></a>
210         <code class="function">crc32c</code> ( <code class="type">bytea</code> )
211         → <code class="returnvalue">bigint</code>
212        </p>
213        <p>
214         Computes the CRC-32C value of the binary string.
215        </p>
216        <p>
217         <code class="literal">crc32c('abc'::bytea)</code>
218         → <code class="returnvalue">910901175</code>
219        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
220         <a id="id-1.5.8.11.7.2.2.4.1.1.1" class="indexterm"></a>
221         <code class="function">get_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
222         <em class="parameter"><code>n</code></em> <code class="type">bigint</code> )
223         → <code class="returnvalue">integer</code>
224        </p>
225        <p>
226         Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit
227         from binary string.
228        </p>
229        <p>
230         <code class="literal">get_bit('\x1234567890'::bytea, 30)</code>
231         → <code class="returnvalue">1</code>
232        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
233         <a id="id-1.5.8.11.7.2.2.5.1.1.1" class="indexterm"></a>
234         <code class="function">get_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
235         <em class="parameter"><code>n</code></em> <code class="type">integer</code> )
236         → <code class="returnvalue">integer</code>
237        </p>
238        <p>
239         Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte
240         from binary string.
241        </p>
242        <p>
243         <code class="literal">get_byte('\x1234567890'::bytea, 4)</code>
244         → <code class="returnvalue">144</code>
245        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
246         <a id="id-1.5.8.11.7.2.2.6.1.1.1" class="indexterm"></a>
247         <a id="id-1.5.8.11.7.2.2.6.1.1.2" class="indexterm"></a>
248         <a id="id-1.5.8.11.7.2.2.6.1.1.3" class="indexterm"></a>
249         <code class="function">length</code> ( <code class="type">bytea</code> )
250         → <code class="returnvalue">integer</code>
251        </p>
252        <p>
253         Returns the number of bytes in the binary string.
254        </p>
255        <p>
256         <code class="literal">length('\x1234567890'::bytea)</code>
257         → <code class="returnvalue">5</code>
258        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
259         <code class="function">length</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
260         <em class="parameter"><code>encoding</code></em> <code class="type">name</code> )
261         → <code class="returnvalue">integer</code>
262        </p>
263        <p>
264         Returns the number of characters in the binary string, assuming
265         that it is text in the given <em class="parameter"><code>encoding</code></em>.
266        </p>
267        <p>
268         <code class="literal">length('jose'::bytea, 'UTF8')</code>
269         → <code class="returnvalue">4</code>
270        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
271         <a id="id-1.5.8.11.7.2.2.8.1.1.1" class="indexterm"></a>
272         <code class="function">md5</code> ( <code class="type">bytea</code> )
273         → <code class="returnvalue">text</code>
274        </p>
275        <p>
276         Computes the MD5 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a> of
277         the binary string, with the result written in hexadecimal.
278        </p>
279        <p>
280         <code class="literal">md5('Th\000omas'::bytea)</code>
281         → <code class="returnvalue">8ab2d3c9689aaf18​b4958c334c82d8b1</code>
282        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
283         <a id="id-1.5.8.11.7.2.2.9.1.1.1" class="indexterm"></a>
284         <code class="function">reverse</code> ( <code class="type">bytea</code> )
285         → <code class="returnvalue">bytea</code>
286        </p>
287        <p>
288         Reverses the order of the bytes in the binary string.
289        </p>
290        <p>
291         <code class="literal">reverse('\xabcd'::bytea)</code>
292         → <code class="returnvalue">\xcdab</code>
293        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
294         <a id="id-1.5.8.11.7.2.2.10.1.1.1" class="indexterm"></a>
295         <code class="function">set_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
296         <em class="parameter"><code>n</code></em> <code class="type">bigint</code>,
297         <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )
298         → <code class="returnvalue">bytea</code>
299        </p>
300        <p>
301         Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit in
302         binary string to <em class="parameter"><code>newvalue</code></em>.
303        </p>
304        <p>
305         <code class="literal">set_bit('\x1234567890'::bytea, 30, 0)</code>
306         → <code class="returnvalue">\x1234563890</code>
307        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
308         <a id="id-1.5.8.11.7.2.2.11.1.1.1" class="indexterm"></a>
309         <code class="function">set_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
310         <em class="parameter"><code>n</code></em> <code class="type">integer</code>,
311         <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )
312         → <code class="returnvalue">bytea</code>
313        </p>
314        <p>
315         Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte in
316         binary string to <em class="parameter"><code>newvalue</code></em>.
317        </p>
318        <p>
319         <code class="literal">set_byte('\x1234567890'::bytea, 4, 64)</code>
320         → <code class="returnvalue">\x1234567840</code>
321        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
322         <a id="id-1.5.8.11.7.2.2.12.1.1.1" class="indexterm"></a>
323         <code class="function">sha224</code> ( <code class="type">bytea</code> )
324         → <code class="returnvalue">bytea</code>
325        </p>
326        <p>
327         Computes the SHA-224 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
328         of the binary string.
329        </p>
330        <p>
331         <code class="literal">sha224('abc'::bytea)</code>
332         → <code class="returnvalue">\x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7</code>
333        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
334         <a id="id-1.5.8.11.7.2.2.13.1.1.1" class="indexterm"></a>
335         <code class="function">sha256</code> ( <code class="type">bytea</code> )
336         → <code class="returnvalue">bytea</code>
337        </p>
338        <p>
339         Computes the SHA-256 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
340         of the binary string.
341        </p>
342        <p>
343         <code class="literal">sha256('abc'::bytea)</code>
344         → <code class="returnvalue">\xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad</code>
345        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
346         <a id="id-1.5.8.11.7.2.2.14.1.1.1" class="indexterm"></a>
347         <code class="function">sha384</code> ( <code class="type">bytea</code> )
348         → <code class="returnvalue">bytea</code>
349        </p>
350        <p>
351         Computes the SHA-384 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
352         of the binary string.
353        </p>
354        <p>
355         <code class="literal">sha384('abc'::bytea)</code>
356         → <code class="returnvalue">\xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7</code>
357        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
358         <a id="id-1.5.8.11.7.2.2.15.1.1.1" class="indexterm"></a>
359         <code class="function">sha512</code> ( <code class="type">bytea</code> )
360         → <code class="returnvalue">bytea</code>
361        </p>
362        <p>
363         Computes the SHA-512 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>
364         of the binary string.
365        </p>
366        <p>
367         <code class="literal">sha512('abc'::bytea)</code>
368         → <code class="returnvalue">\xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f</code>
369        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
370         <a id="id-1.5.8.11.7.2.2.16.1.1.1" class="indexterm"></a>
371         <code class="function">substr</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code> [<span class="optional">, <em class="parameter"><code>count</code></em> <code class="type">integer</code> </span>] )
372         → <code class="returnvalue">bytea</code>
373        </p>
374        <p>
375         Extracts the substring of <em class="parameter"><code>bytes</code></em> starting at
376         the <em class="parameter"><code>start</code></em>'th byte,
377         and extending for <em class="parameter"><code>count</code></em> bytes if that is
378         specified.  (Same
379         as <code class="literal">substring(<em class="parameter"><code>bytes</code></em>
380         from <em class="parameter"><code>start</code></em>
381         for <em class="parameter"><code>count</code></em>)</code>.)
382        </p>
383        <p>
384         <code class="literal">substr('\x1234567890'::bytea, 3, 2)</code>
385         → <code class="returnvalue">\x5678</code>
386        </p></td></tr></tbody></table></div></div><br class="table-break" /><p id="FUNCTIONS-ZEROBASED-NOTE">
387    Functions <code class="function">get_byte</code> and <code class="function">set_byte</code>
388    number the first byte of a binary string as byte 0.
389    Functions <code class="function">get_bit</code> and <code class="function">set_bit</code>
390    number bits from the right within each byte; for example bit 0 is the least
391    significant bit of the first byte, and bit 15 is the most significant bit
392    of the second byte.
393   </p><p id="FUNCTIONS-HASH-NOTE">
394    For historical reasons, the function <code class="function">md5</code>
395    returns a hex-encoded value of type <code class="type">text</code> whereas the SHA-2
396    functions return type <code class="type">bytea</code>.  Use the functions
397    <a class="link" href="functions-binarystring.html#FUNCTION-ENCODE"><code class="function">encode</code></a>
398    and <a class="link" href="functions-binarystring.html#FUNCTION-DECODE"><code class="function">decode</code></a> to
399    convert between the two.  For example write <code class="literal">encode(sha256('abc'),
400    'hex')</code> to get a hex-encoded text representation,
401    or <code class="literal">decode(md5('abc'), 'hex')</code> to get
402    a <code class="type">bytea</code> value.
403   </p><p>
404    <a id="id-1.5.8.11.10.1" class="indexterm"></a>
405    <a id="id-1.5.8.11.10.2" class="indexterm"></a>
406    Functions for converting strings between different character sets
407    (encodings), and for representing arbitrary binary data in textual
408    form, are shown in
409    <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS" title="Table 9.13. Text/Binary String Conversion Functions">Table 9.13</a>.  For these
410    functions, an argument or result of type <code class="type">text</code> is expressed
411    in the database's default encoding, while arguments or results of
412    type <code class="type">bytea</code> are in an encoding named by another argument.
413   </p><div class="table" id="FUNCTIONS-BINARYSTRING-CONVERSIONS"><p class="title"><strong>Table 9.13. Text/Binary String Conversion Functions</strong></p><div class="table-contents"><table class="table" summary="Text/Binary String Conversion Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
414        Function
415       </p>
416       <p>
417        Description
418       </p>
419       <p>
420        Example(s)
421       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
422        <a id="id-1.5.8.11.11.2.2.1.1.1.1" class="indexterm"></a>
423        <code class="function">convert</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
424        <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code>,
425        <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )
426        → <code class="returnvalue">bytea</code>
427       </p>
428       <p>
429        Converts a binary string representing text in
430        encoding <em class="parameter"><code>src_encoding</code></em>
431        to a binary string in encoding <em class="parameter"><code>dest_encoding</code></em>
432        (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="23.3.4. Available Character Set Conversions">Section 23.3.4</a> for
433        available conversions).
434       </p>
435       <p>
436        <code class="literal">convert('text_in_utf8', 'UTF8', 'LATIN1')</code>
437        → <code class="returnvalue">\x746578745f696e5f75746638</code>
438       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
439        <a id="id-1.5.8.11.11.2.2.2.1.1.1" class="indexterm"></a>
440        <code class="function">convert_from</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
441        <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code> )
442        → <code class="returnvalue">text</code>
443       </p>
444       <p>
445        Converts a binary string representing text in
446        encoding <em class="parameter"><code>src_encoding</code></em>
447        to <code class="type">text</code> in the database encoding
448        (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="23.3.4. Available Character Set Conversions">Section 23.3.4</a> for
449        available conversions).
450       </p>
451       <p>
452        <code class="literal">convert_from('text_in_utf8', 'UTF8')</code>
453        → <code class="returnvalue">text_in_utf8</code>
454       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
455        <a id="id-1.5.8.11.11.2.2.3.1.1.1" class="indexterm"></a>
456        <code class="function">convert_to</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,
457        <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )
458        → <code class="returnvalue">bytea</code>
459       </p>
460       <p>
461        Converts a <code class="type">text</code> string (in the database encoding) to a
462        binary string encoded in encoding <em class="parameter"><code>dest_encoding</code></em>
463        (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="23.3.4. Available Character Set Conversions">Section 23.3.4</a> for
464        available conversions).
465       </p>
466       <p>
467        <code class="literal">convert_to('some_text', 'UTF8')</code>
468        → <code class="returnvalue">\x736f6d655f74657874</code>
469       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
470        <a id="FUNCTION-ENCODE" class="indexterm"></a>
471        <code class="function">encode</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,
472        <em class="parameter"><code>format</code></em> <code class="type">text</code> )
473        → <code class="returnvalue">text</code>
474       </p>
475       <p>
476        Encodes binary data into a textual representation; supported
477        <em class="parameter"><code>format</code></em> values are:
478        <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-BASE64"><code class="literal">base64</code></a>,
479        <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-ESCAPE"><code class="literal">escape</code></a>,
480        <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-HEX"><code class="literal">hex</code></a>.
481       </p>
482       <p>
483        <code class="literal">encode('123\000\001', 'base64')</code>
484        → <code class="returnvalue">MTIzAAE=</code>
485       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
486        <a id="FUNCTION-DECODE" class="indexterm"></a>
487        <code class="function">decode</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,
488        <em class="parameter"><code>format</code></em> <code class="type">text</code> )
489        → <code class="returnvalue">bytea</code>
490       </p>
491       <p>
492        Decodes binary data from a textual representation; supported
493        <em class="parameter"><code>format</code></em> values are the same as
494        for <code class="function">encode</code>.
495       </p>
496       <p>
497        <code class="literal">decode('MTIzAAE=', 'base64')</code>
498        → <code class="returnvalue">\x3132330001</code>
499       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
500    The <code class="function">encode</code> and <code class="function">decode</code>
501    functions support the following textual formats:
502
503    </p><div class="variablelist"><dl class="variablelist"><dt id="ENCODE-FORMAT-BASE64"><span class="term">base64
504      <a id="id-1.5.8.11.12.3.1.1.1" class="indexterm"></a></span> <a href="#ENCODE-FORMAT-BASE64" class="id_link">#</a></dt><dd><p>
505        The <code class="literal">base64</code> format is that
506        of <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc2045#section-6.8" target="_top">RFC
507        2045 Section 6.8</a>.  As per the <acronym class="acronym">RFC</acronym>, encoded lines are
508        broken at 76 characters.  However instead of the MIME CRLF
509        end-of-line marker, only a newline is used for end-of-line.
510        The <code class="function">decode</code> function ignores carriage-return,
511        newline, space, and tab characters.  Otherwise, an error is
512        raised when <code class="function">decode</code> is supplied invalid
513        base64 data — including when trailing padding is incorrect.
514       </p></dd><dt id="ENCODE-FORMAT-ESCAPE"><span class="term">escape
515      <a id="id-1.5.8.11.12.3.2.1.1" class="indexterm"></a></span> <a href="#ENCODE-FORMAT-ESCAPE" class="id_link">#</a></dt><dd><p>
516        The <code class="literal">escape</code> format converts zero bytes and
517        bytes with the high bit set into octal escape sequences
518        (<code class="literal">\</code><em class="replaceable"><code>nnn</code></em>), and it doubles
519        backslashes.  Other byte values are represented literally.
520        The <code class="function">decode</code> function will raise an error if a
521        backslash is not followed by either a second backslash or three
522        octal digits; it accepts other byte values unchanged.
523       </p></dd><dt id="ENCODE-FORMAT-HEX"><span class="term">hex
524      <a id="id-1.5.8.11.12.3.3.1.1" class="indexterm"></a></span> <a href="#ENCODE-FORMAT-HEX" class="id_link">#</a></dt><dd><p>
525        The <code class="literal">hex</code> format represents each 4 bits of
526        data as one hexadecimal digit, <code class="literal">0</code>
527        through <code class="literal">f</code>, writing the higher-order digit of
528        each byte first.  The <code class="function">encode</code> function outputs
529        the <code class="literal">a</code>-<code class="literal">f</code> hex digits in lower
530        case.  Because the smallest unit of data is 8 bits, there are
531        always an even number of characters returned
532        by <code class="function">encode</code>.
533        The <code class="function">decode</code> function
534        accepts the <code class="literal">a</code>-<code class="literal">f</code> characters in
535        either upper or lower case.  An error is raised
536        when <code class="function">decode</code> is given invalid hex data
537        — including when given an odd number of characters.
538       </p></dd></dl></div><p>
539   </p><p>
540    In addition, it is possible to cast integral values to and from type
541    <code class="type">bytea</code>. Casting an integer to <code class="type">bytea</code> produces
542    2, 4, or 8 bytes, depending on the width of the integer type. The result
543    is the two's complement representation of the integer, with the most
544    significant byte first. Some examples:
545 </p><pre class="programlisting">
546 1234::smallint::bytea          <em class="lineannotation"><span class="lineannotation">\x04d2</span></em>
547 cast(1234 as bytea)            <em class="lineannotation"><span class="lineannotation">\x000004d2</span></em>
548 cast(-1234 as bytea)           <em class="lineannotation"><span class="lineannotation">\xfffffb2e</span></em>
549 '\x8000'::bytea::smallint      <em class="lineannotation"><span class="lineannotation">-32768</span></em>
550 '\x8000'::bytea::integer       <em class="lineannotation"><span class="lineannotation">32768</span></em>
551 </pre><p>
552    Casting a <code class="type">bytea</code> to an integer will raise an error if the
553    length of the <code class="type">bytea</code> exceeds the width of the integer type.
554   </p><p>
555    See also the aggregate function <code class="function">string_agg</code> in
556    <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> and the large object functions
557    in <a class="xref" href="lo-funcs.html" title="33.4. Server-Side Functions">Section 33.4</a>.
558   </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" title="9.4. String Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-bitstring.html" title="9.6. Bit String Functions and Operators">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" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.6. Bit String Functions and Operators</td></tr></table></div></body></html>
full postgres docs in easy to read format with index