Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-binarystring.html510 linesDownload Raw Back to 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 16.3 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 and4    manipulating binary strings, that is values of type <code class="type">bytea</code>.5    Many of these are equivalent, in purpose and syntax, to the6    text-string functions described in the previous section.7   </p><p>8    <acronym class="acronym">SQL</acronym> defines some string functions that use9    key words, rather than commas, to separate10    arguments.  Details are in11    <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 functions13    that use the regular function invocation syntax14    (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/Operator17       </p>18       <p>19        Description20       </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 (841        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 in54        <em class="parameter"><code>bytesremoved</code></em> from the start and end of55        <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 in68         <em class="parameter"><code>bytesremoved</code></em> from the start of69         <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 at92        the <em class="parameter"><code>start</code></em>'th byte and extends93        for <em class="parameter"><code>count</code></em> bytes94        with <em class="parameter"><code>newsubstring</code></em>.95        If <em class="parameter"><code>count</code></em> is omitted, it defaults to the length96        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 specified108        <em class="parameter"><code>substring</code></em> within109        <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 in122         <em class="parameter"><code>bytesremoved</code></em> from the end of123         <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 at135        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 is137        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 in152        <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 and173    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>.  Some174    of them are used internally to implement the175    <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        Function178       </p>179       <p>180        Description181       </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 as192        <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">get_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,200        <em class="parameter"><code>n</code></em> <code class="type">bigint</code> )201        → <code class="returnvalue">integer</code>202       </p>203       <p>204        Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit205        from binary string.206       </p>207       <p>208        <code class="literal">get_bit('\x1234567890'::bytea, 30)</code>209        → <code class="returnvalue">1</code>210       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">211        <a id="id-1.5.8.11.7.2.2.3.1.1.1" class="indexterm"></a>212        <code class="function">get_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,213        <em class="parameter"><code>n</code></em> <code class="type">integer</code> )214        → <code class="returnvalue">integer</code>215       </p>216       <p>217        Extracts <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte218        from binary string.219       </p>220       <p>221        <code class="literal">get_byte('\x1234567890'::bytea, 4)</code>222        → <code class="returnvalue">144</code>223       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">224        <a id="id-1.5.8.11.7.2.2.4.1.1.1" class="indexterm"></a>225        <a id="id-1.5.8.11.7.2.2.4.1.1.2" class="indexterm"></a>226        <a id="id-1.5.8.11.7.2.2.4.1.1.3" class="indexterm"></a>227        <code class="function">length</code> ( <code class="type">bytea</code> )228        → <code class="returnvalue">integer</code>229       </p>230       <p>231        Returns the number of bytes in the binary string.232       </p>233       <p>234        <code class="literal">length('\x1234567890'::bytea)</code>235        → <code class="returnvalue">5</code>236       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">237        <code class="function">length</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,238        <em class="parameter"><code>encoding</code></em> <code class="type">name</code> )239        → <code class="returnvalue">integer</code>240       </p>241       <p>242        Returns the number of characters in the binary string, assuming243        that it is text in the given <em class="parameter"><code>encoding</code></em>.244       </p>245       <p>246        <code class="literal">length('jose'::bytea, 'UTF8')</code>247        → <code class="returnvalue">4</code>248       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">249        <a id="id-1.5.8.11.7.2.2.6.1.1.1" class="indexterm"></a>250        <code class="function">md5</code> ( <code class="type">bytea</code> )251        → <code class="returnvalue">text</code>252       </p>253       <p>254        Computes the MD5 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a> of255        the binary string, with the result written in hexadecimal.256       </p>257       <p>258        <code class="literal">md5('Th\000omas'::bytea)</code>259        → <code class="returnvalue">8ab2d3c9689aaf18​b4958c334c82d8b1</code>260       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">261        <a id="id-1.5.8.11.7.2.2.7.1.1.1" class="indexterm"></a>262        <code class="function">set_bit</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,263        <em class="parameter"><code>n</code></em> <code class="type">bigint</code>,264        <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )265        → <code class="returnvalue">bytea</code>266       </p>267       <p>268        Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> bit in269        binary string to <em class="parameter"><code>newvalue</code></em>.270       </p>271       <p>272        <code class="literal">set_bit('\x1234567890'::bytea, 30, 0)</code>273        → <code class="returnvalue">\x1234563890</code>274       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">275        <a id="id-1.5.8.11.7.2.2.8.1.1.1" class="indexterm"></a>276        <code class="function">set_byte</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,277        <em class="parameter"><code>n</code></em> <code class="type">integer</code>,278        <em class="parameter"><code>newvalue</code></em> <code class="type">integer</code> )279        → <code class="returnvalue">bytea</code>280       </p>281       <p>282        Sets <a class="link" href="functions-binarystring.html#FUNCTIONS-ZEROBASED-NOTE">n'th</a> byte in283        binary string to <em class="parameter"><code>newvalue</code></em>.284       </p>285       <p>286        <code class="literal">set_byte('\x1234567890'::bytea, 4, 64)</code>287        → <code class="returnvalue">\x1234567840</code>288       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">289        <a id="id-1.5.8.11.7.2.2.9.1.1.1" class="indexterm"></a>290        <code class="function">sha224</code> ( <code class="type">bytea</code> )291        → <code class="returnvalue">bytea</code>292       </p>293       <p>294        Computes the SHA-224 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>295        of the binary string.296       </p>297       <p>298        <code class="literal">sha224('abc'::bytea)</code>299        → <code class="returnvalue">\x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7</code>300       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">301        <a id="id-1.5.8.11.7.2.2.10.1.1.1" class="indexterm"></a>302        <code class="function">sha256</code> ( <code class="type">bytea</code> )303        → <code class="returnvalue">bytea</code>304       </p>305       <p>306        Computes the SHA-256 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>307        of the binary string.308       </p>309       <p>310        <code class="literal">sha256('abc'::bytea)</code>311        → <code class="returnvalue">\xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad</code>312       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">313        <a id="id-1.5.8.11.7.2.2.11.1.1.1" class="indexterm"></a>314        <code class="function">sha384</code> ( <code class="type">bytea</code> )315        → <code class="returnvalue">bytea</code>316       </p>317       <p>318        Computes the SHA-384 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>319        of the binary string.320       </p>321       <p>322        <code class="literal">sha384('abc'::bytea)</code>323        → <code class="returnvalue">\xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7</code>324       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">325        <a id="id-1.5.8.11.7.2.2.12.1.1.1" class="indexterm"></a>326        <code class="function">sha512</code> ( <code class="type">bytea</code> )327        → <code class="returnvalue">bytea</code>328       </p>329       <p>330        Computes the SHA-512 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a>331        of the binary string.332       </p>333       <p>334        <code class="literal">sha512('abc'::bytea)</code>335        → <code class="returnvalue">\xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f</code>336       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">337        <a id="id-1.5.8.11.7.2.2.13.1.1.1" class="indexterm"></a>338        <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>] )339        → <code class="returnvalue">bytea</code>340       </p>341       <p>342        Extracts the substring of <em class="parameter"><code>bytes</code></em> starting at343        the <em class="parameter"><code>start</code></em>'th byte,344        and extending for <em class="parameter"><code>count</code></em> bytes if that is345        specified.  (Same346        as <code class="literal">substring(<em class="parameter"><code>bytes</code></em>347        from <em class="parameter"><code>start</code></em>348        for <em class="parameter"><code>count</code></em>)</code>.)349       </p>350       <p>351        <code class="literal">substr('\x1234567890'::bytea, 3, 2)</code>352        → <code class="returnvalue">\x5678</code>353       </p></td></tr></tbody></table></div></div><br class="table-break" /><p id="FUNCTIONS-ZEROBASED-NOTE">354   Functions <code class="function">get_byte</code> and <code class="function">set_byte</code>355   number the first byte of a binary string as byte 0.356   Functions <code class="function">get_bit</code> and <code class="function">set_bit</code>357   number bits from the right within each byte; for example bit 0 is the least358   significant bit of the first byte, and bit 15 is the most significant bit359   of the second byte.360  </p><p id="FUNCTIONS-HASH-NOTE">361   For historical reasons, the function <code class="function">md5</code>362   returns a hex-encoded value of type <code class="type">text</code> whereas the SHA-2363   functions return type <code class="type">bytea</code>.  Use the functions364   <a class="link" href="functions-binarystring.html#FUNCTION-ENCODE"><code class="function">encode</code></a>365   and <a class="link" href="functions-binarystring.html#FUNCTION-DECODE"><code class="function">decode</code></a> to366   convert between the two.  For example write <code class="literal">encode(sha256('abc'),367   'hex')</code> to get a hex-encoded text representation,368   or <code class="literal">decode(md5('abc'), 'hex')</code> to get369   a <code class="type">bytea</code> value.370  </p><p>371   <a id="id-1.5.8.11.10.1" class="indexterm"></a>372   <a id="id-1.5.8.11.10.2" class="indexterm"></a>373   Functions for converting strings between different character sets374   (encodings), and for representing arbitrary binary data in textual375   form, are shown in376   <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS" title="Table 9.13. Text/Binary String Conversion Functions">Table 9.13</a>.  For these377   functions, an argument or result of type <code class="type">text</code> is expressed378   in the database's default encoding, while arguments or results of379   type <code class="type">bytea</code> are in an encoding named by another argument.380  </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">381       Function382      </p>383      <p>384       Description385      </p>386      <p>387       Example(s)388      </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">389       <a id="id-1.5.8.11.11.2.2.1.1.1.1" class="indexterm"></a>390       <code class="function">convert</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,391       <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code>,392       <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )393       → <code class="returnvalue">bytea</code>394      </p>395      <p>396       Converts a binary string representing text in397       encoding <em class="parameter"><code>src_encoding</code></em>398       to a binary string in encoding <em class="parameter"><code>dest_encoding</code></em>399       (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="24.3.4. Available Character Set Conversions">Section 24.3.4</a> for400       available conversions).401      </p>402      <p>403       <code class="literal">convert('text_in_utf8', 'UTF8', 'LATIN1')</code>404       → <code class="returnvalue">\x746578745f696e5f75746638</code>405      </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">406       <a id="id-1.5.8.11.11.2.2.2.1.1.1" class="indexterm"></a>407       <code class="function">convert_from</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,408       <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code> )409       → <code class="returnvalue">text</code>410      </p>411      <p>412       Converts a binary string representing text in413       encoding <em class="parameter"><code>src_encoding</code></em>414       to <code class="type">text</code> in the database encoding415       (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="24.3.4. Available Character Set Conversions">Section 24.3.4</a> for416       available conversions).417      </p>418      <p>419       <code class="literal">convert_from('text_in_utf8', 'UTF8')</code>420       → <code class="returnvalue">text_in_utf8</code>421      </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">422       <a id="id-1.5.8.11.11.2.2.3.1.1.1" class="indexterm"></a>423       <code class="function">convert_to</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,424       <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code> )425       → <code class="returnvalue">bytea</code>426      </p>427      <p>428       Converts a <code class="type">text</code> string (in the database encoding) to a429       binary string encoded in encoding <em class="parameter"><code>dest_encoding</code></em>430       (see <a class="xref" href="multibyte.html#MULTIBYTE-CONVERSIONS-SUPPORTED" title="24.3.4. Available Character Set Conversions">Section 24.3.4</a> for431       available conversions).432      </p>433      <p>434       <code class="literal">convert_to('some_text', 'UTF8')</code>435       → <code class="returnvalue">\x736f6d655f74657874</code>436      </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">437       <a id="FUNCTION-ENCODE" class="indexterm"></a>438       <code class="function">encode</code> ( <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>,439       <em class="parameter"><code>format</code></em> <code class="type">text</code> )440       → <code class="returnvalue">text</code>441      </p>442      <p>443       Encodes binary data into a textual representation; supported444       <em class="parameter"><code>format</code></em> values are:445       <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-BASE64"><code class="literal">base64</code></a>,446       <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-ESCAPE"><code class="literal">escape</code></a>,447       <a class="link" href="functions-binarystring.html#ENCODE-FORMAT-HEX"><code class="literal">hex</code></a>.448      </p>449      <p>450       <code class="literal">encode('123\000\001', 'base64')</code>451       → <code class="returnvalue">MTIzAAE=</code>452      </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">453       <a id="FUNCTION-DECODE" class="indexterm"></a>454       <code class="function">decode</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,455       <em class="parameter"><code>format</code></em> <code class="type">text</code> )456       → <code class="returnvalue">bytea</code>457      </p>458      <p>459       Decodes binary data from a textual representation; supported460       <em class="parameter"><code>format</code></em> values are the same as461       for <code class="function">encode</code>.462      </p>463      <p>464       <code class="literal">decode('MTIzAAE=', 'base64')</code>465       → <code class="returnvalue">\x3132330001</code>466      </p></td></tr></tbody></table></div></div><br class="table-break" /><p>467   The <code class="function">encode</code> and <code class="function">decode</code>468   functions support the following textual formats:469 470   </p><div class="variablelist"><dl class="variablelist"><dt id="ENCODE-FORMAT-BASE64"><span class="term">base64471     <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>472       The <code class="literal">base64</code> format is that473       of <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc2045#section-6.8" target="_top">RFC474       2045 Section 6.8</a>.  As per the <acronym class="acronym">RFC</acronym>, encoded lines are475       broken at 76 characters.  However instead of the MIME CRLF476       end-of-line marker, only a newline is used for end-of-line.477       The <code class="function">decode</code> function ignores carriage-return,478       newline, space, and tab characters.  Otherwise, an error is479       raised when <code class="function">decode</code> is supplied invalid480       base64 data — including when trailing padding is incorrect.481      </p></dd><dt id="ENCODE-FORMAT-ESCAPE"><span class="term">escape482     <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>483       The <code class="literal">escape</code> format converts zero bytes and484       bytes with the high bit set into octal escape sequences485       (<code class="literal">\</code><em class="replaceable"><code>nnn</code></em>), and it doubles486       backslashes.  Other byte values are represented literally.487       The <code class="function">decode</code> function will raise an error if a488       backslash is not followed by either a second backslash or three489       octal digits; it accepts other byte values unchanged.490      </p></dd><dt id="ENCODE-FORMAT-HEX"><span class="term">hex491     <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>492       The <code class="literal">hex</code> format represents each 4 bits of493       data as one hexadecimal digit, <code class="literal">0</code>494       through <code class="literal">f</code>, writing the higher-order digit of495       each byte first.  The <code class="function">encode</code> function outputs496       the <code class="literal">a</code>-<code class="literal">f</code> hex digits in lower497       case.  Because the smallest unit of data is 8 bits, there are498       always an even number of characters returned499       by <code class="function">encode</code>.500       The <code class="function">decode</code> function501       accepts the <code class="literal">a</code>-<code class="literal">f</code> characters in502       either upper or lower case.  An error is raised503       when <code class="function">decode</code> is given invalid hex data504       — including when given an odd number of characters.505      </p></dd></dl></div><p>506  </p><p>507   See also the aggregate function <code class="function">string_agg</code> in508   <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> and the large object functions509   in <a class="xref" href="lo-funcs.html" title="35.4. Server-Side Functions">Section 35.4</a>.510  </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.6. Bit String Functions and Operators</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai