Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-string.html1208 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.4. 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-math.html" title="9.3. Mathematical Functions and Operators" /><link rel="next" href="functions-binarystring.html" title="9.5. Binary 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.4. String Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-math.html" title="9.3. Mathematical 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-binarystring.html" title="9.5. Binary String Functions and Operators">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-STRING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.4. String Functions and Operators <a href="#FUNCTIONS-STRING" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-string.html#FUNCTIONS-STRING-FORMAT">9.4.1. <code class="function">format</code></a></span></dt></dl></div><p>3    This section describes functions and operators for examining and4    manipulating string values.  Strings in this context include values5    of the types <code class="type">character</code>, <code class="type">character varying</code>,6    and <code class="type">text</code>.  Except where noted, these functions and operators7    are declared to accept and return type <code class="type">text</code>.  They will8    interchangeably accept <code class="type">character varying</code> arguments.9    Values of type <code class="type">character</code> will be converted10    to <code class="type">text</code> before the function or operator is applied, resulting11    in stripping any trailing spaces in the <code class="type">character</code> value.12   </p><p>13    <acronym class="acronym">SQL</acronym> defines some string functions that use14    key words, rather than commas, to separate15    arguments.  Details are in16    <a class="xref" href="functions-string.html#FUNCTIONS-STRING-SQL" title="Table 9.9. SQL String Functions and Operators">Table 9.9</a>.17    <span class="productname">PostgreSQL</span> also provides versions of these functions18    that use the regular function invocation syntax19    (see <a class="xref" href="functions-string.html#FUNCTIONS-STRING-OTHER" title="Table 9.10. Other String Functions and Operators">Table 9.10</a>).20   </p><div class="note"><h3 class="title">Note</h3><p>21     The string concatenation operator (<code class="literal">||</code>) will accept22     non-string input, so long as at least one input is of string type, as shown23     in <a class="xref" href="functions-string.html#FUNCTIONS-STRING-SQL" title="Table 9.9. SQL String Functions and Operators">Table 9.9</a>.  For other cases, inserting an24     explicit coercion to <code class="type">text</code> can be used to have non-string input25     accepted.26    </p></div><div class="table" id="FUNCTIONS-STRING-SQL"><p class="title"><strong>Table 9.9. <acronym class="acronym">SQL</acronym> String Functions and Operators</strong></p><div class="table-contents"><table class="table" summary="SQL String Functions and Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">27        Function/Operator28       </p>29       <p>30        Description31       </p>32       <p>33        Example(s)34       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">35        <a id="id-1.5.8.10.5.2.2.1.1.1.1" class="indexterm"></a>36        <code class="type">text</code> <code class="literal">||</code> <code class="type">text</code>37        → <code class="returnvalue">text</code>38       </p>39       <p>40        Concatenates the two strings.41       </p>42       <p>43        <code class="literal">'Post' || 'greSQL'</code>44        → <code class="returnvalue">PostgreSQL</code>45       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">46        <code class="type">text</code> <code class="literal">||</code> <code class="type">anynonarray</code>47        → <code class="returnvalue">text</code>48       </p>49       <p class="func_signature">50        <code class="type">anynonarray</code> <code class="literal">||</code> <code class="type">text</code>51        → <code class="returnvalue">text</code>52       </p>53       <p>54        Converts the non-string input to text, then concatenates the two55        strings.  (The non-string input cannot be of an array type, because56        that would create ambiguity with the array <code class="literal">||</code>57        operators.  If you want to concatenate an array's text equivalent,58        cast it to <code class="type">text</code> explicitly.)59       </p>60       <p>61        <code class="literal">'Value: ' || 42</code>62        → <code class="returnvalue">Value: 42</code>63       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">64        <a id="id-1.5.8.10.5.2.2.3.1.1.1" class="indexterm"></a>65        <code class="function">btrim</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>66        [<span class="optional">, <em class="parameter"><code>characters</code></em> <code class="type">text</code> </span>] )67        → <code class="returnvalue">text</code>68       </p>69       <p>70        Removes the longest string containing only characters71        in <em class="parameter"><code>characters</code></em> (a space by default)72        from the start and end of <em class="parameter"><code>string</code></em>.73       </p>74       <p>75        <code class="literal">btrim('xyxtrimyyx', 'xyz')</code>76        → <code class="returnvalue">trim</code>77       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">78        <a id="id-1.5.8.10.5.2.2.4.1.1.1" class="indexterm"></a>79        <a id="id-1.5.8.10.5.2.2.4.1.1.2" class="indexterm"></a>80         <code class="type">text</code> <code class="literal">IS</code> [<span class="optional"><code class="literal">NOT</code></span>] [<span class="optional"><em class="parameter"><code>form</code></em></span>] <code class="literal">NORMALIZED</code>81        → <code class="returnvalue">boolean</code>82       </p>83       <p>84        Checks whether the string is in the specified Unicode normalization85        form.  The optional <em class="parameter"><code>form</code></em> key word specifies the86        form: <code class="literal">NFC</code> (the default), <code class="literal">NFD</code>,87        <code class="literal">NFKC</code>, or <code class="literal">NFKD</code>.  This expression can88        only be used when the server encoding is <code class="literal">UTF8</code>.  Note89        that checking for normalization using this expression is often faster90        than normalizing possibly already normalized strings.91       </p>92       <p>93        <code class="literal">U&amp;'\0061\0308bc' IS NFD NORMALIZED</code>94        → <code class="returnvalue">t</code>95       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">96        <a id="id-1.5.8.10.5.2.2.5.1.1.1" class="indexterm"></a>97        <code class="function">bit_length</code> ( <code class="type">text</code> )98        → <code class="returnvalue">integer</code>99       </p>100       <p>101        Returns number of bits in the string (8102        times the <code class="function">octet_length</code>).103       </p>104       <p>105        <code class="literal">bit_length('jose')</code>106        → <code class="returnvalue">32</code>107       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">108        <a id="id-1.5.8.10.5.2.2.6.1.1.1" class="indexterm"></a>109        <a id="id-1.5.8.10.5.2.2.6.1.1.2" class="indexterm"></a>110        <a id="id-1.5.8.10.5.2.2.6.1.1.3" class="indexterm"></a>111        <code class="function">char_length</code> ( <code class="type">text</code> )112        → <code class="returnvalue">integer</code>113       </p>114       <p class="func_signature">115        <a id="id-1.5.8.10.5.2.2.6.1.2.1" class="indexterm"></a>116        <code class="function">character_length</code> ( <code class="type">text</code> )117        → <code class="returnvalue">integer</code>118       </p>119       <p>120        Returns number of characters in the string.121       </p>122       <p>123        <code class="literal">char_length('josé')</code>124        → <code class="returnvalue">4</code>125       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">126        <a id="id-1.5.8.10.5.2.2.7.1.1.1" class="indexterm"></a>127        <code class="function">lower</code> ( <code class="type">text</code> )128        → <code class="returnvalue">text</code>129       </p>130       <p>131        Converts the string to all lower case, according to the rules of the132        database's locale.133       </p>134       <p>135        <code class="literal">lower('TOM')</code>136        → <code class="returnvalue">tom</code>137       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">138        <a id="id-1.5.8.10.5.2.2.8.1.1.1" class="indexterm"></a>139        <code class="function">lpad</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,140        <em class="parameter"><code>length</code></em> <code class="type">integer</code>141        [<span class="optional">, <em class="parameter"><code>fill</code></em> <code class="type">text</code> </span>] )142        → <code class="returnvalue">text</code>143       </p>144       <p>145        Extends the <em class="parameter"><code>string</code></em> to length146        <em class="parameter"><code>length</code></em> by prepending the characters147        <em class="parameter"><code>fill</code></em> (a space by default).  If the148        <em class="parameter"><code>string</code></em> is already longer than149        <em class="parameter"><code>length</code></em> then it is truncated (on the right).150       </p>151       <p>152        <code class="literal">lpad('hi', 5, 'xy')</code>153        → <code class="returnvalue">xyxhi</code>154       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">155        <a id="id-1.5.8.10.5.2.2.9.1.1.1" class="indexterm"></a>156        <code class="function">ltrim</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>157        [<span class="optional">, <em class="parameter"><code>characters</code></em> <code class="type">text</code> </span>] )158        → <code class="returnvalue">text</code>159       </p>160       <p>161        Removes the longest string containing only characters in162        <em class="parameter"><code>characters</code></em> (a space by default) from the start of163        <em class="parameter"><code>string</code></em>.164       </p>165       <p>166        <code class="literal">ltrim('zzzytest', 'xyz')</code>167        → <code class="returnvalue">test</code>168       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">169        <a id="id-1.5.8.10.5.2.2.10.1.1.1" class="indexterm"></a>170        <a id="id-1.5.8.10.5.2.2.10.1.1.2" class="indexterm"></a>171        <code class="function">normalize</code> ( <code class="type">text</code>172        [<span class="optional">, <em class="parameter"><code>form</code></em> </span>] )173        → <code class="returnvalue">text</code>174       </p>175       <p>176        Converts the string to the specified Unicode177        normalization form.  The optional <em class="parameter"><code>form</code></em> key word178        specifies the form: <code class="literal">NFC</code> (the default),179        <code class="literal">NFD</code>, <code class="literal">NFKC</code>, or180        <code class="literal">NFKD</code>.  This function can only be used when the181        server encoding is <code class="literal">UTF8</code>.182       </p>183       <p>184        <code class="literal">normalize(U&amp;'\0061\0308bc', NFC)</code>185        → <code class="returnvalue">U&amp;'\00E4bc'</code>186       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">187        <a id="id-1.5.8.10.5.2.2.11.1.1.1" class="indexterm"></a>188        <code class="function">octet_length</code> ( <code class="type">text</code> )189        → <code class="returnvalue">integer</code>190       </p>191       <p>192        Returns number of bytes in the string.193       </p>194       <p>195        <code class="literal">octet_length('josé')</code>196        → <code class="returnvalue">5</code> (if server encoding is UTF8)197       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">198        <a id="id-1.5.8.10.5.2.2.12.1.1.1" class="indexterm"></a>199        <code class="function">octet_length</code> ( <code class="type">character</code> )200        → <code class="returnvalue">integer</code>201       </p>202       <p>203        Returns number of bytes in the string.  Since this version of the204        function accepts type <code class="type">character</code> directly, it will not205        strip trailing spaces.206       </p>207       <p>208        <code class="literal">octet_length('abc '::character(4))</code>209        → <code class="returnvalue">4</code>210       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">211        <a id="id-1.5.8.10.5.2.2.13.1.1.1" class="indexterm"></a>212        <code class="function">overlay</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code> <code class="literal">PLACING</code> <em class="parameter"><code>newsubstring</code></em> <code class="type">text</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>] )213        → <code class="returnvalue">text</code>214       </p>215       <p>216        Replaces the substring of <em class="parameter"><code>string</code></em> that starts at217        the <em class="parameter"><code>start</code></em>'th character and extends218        for <em class="parameter"><code>count</code></em> characters219        with <em class="parameter"><code>newsubstring</code></em>.220        If <em class="parameter"><code>count</code></em> is omitted, it defaults to the length221        of <em class="parameter"><code>newsubstring</code></em>.222       </p>223       <p>224        <code class="literal">overlay('Txxxxas' placing 'hom' from 2 for 4)</code>225        → <code class="returnvalue">Thomas</code>226       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">227        <a id="id-1.5.8.10.5.2.2.14.1.1.1" class="indexterm"></a>228        <code class="function">position</code> ( <em class="parameter"><code>substring</code></em> <code class="type">text</code> <code class="literal">IN</code> <em class="parameter"><code>string</code></em> <code class="type">text</code> )229        → <code class="returnvalue">integer</code>230       </p>231       <p>232        Returns first starting index of the specified233        <em class="parameter"><code>substring</code></em> within234        <em class="parameter"><code>string</code></em>, or zero if it's not present.235       </p>236       <p>237        <code class="literal">position('om' in 'Thomas')</code>238        → <code class="returnvalue">3</code>239       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">240        <a id="id-1.5.8.10.5.2.2.15.1.1.1" class="indexterm"></a>241        <code class="function">rpad</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,242        <em class="parameter"><code>length</code></em> <code class="type">integer</code>243        [<span class="optional">, <em class="parameter"><code>fill</code></em> <code class="type">text</code> </span>] )244        → <code class="returnvalue">text</code>245       </p>246       <p>247        Extends the <em class="parameter"><code>string</code></em> to length248        <em class="parameter"><code>length</code></em> by appending the characters249        <em class="parameter"><code>fill</code></em> (a space by default).  If the250        <em class="parameter"><code>string</code></em> is already longer than251        <em class="parameter"><code>length</code></em> then it is truncated.252       </p>253       <p>254        <code class="literal">rpad('hi', 5, 'xy')</code>255        → <code class="returnvalue">hixyx</code>256       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">257        <a id="id-1.5.8.10.5.2.2.16.1.1.1" class="indexterm"></a>258        <code class="function">rtrim</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>259         [<span class="optional">, <em class="parameter"><code>characters</code></em> <code class="type">text</code> </span>] )260        → <code class="returnvalue">text</code>261       </p>262       <p>263        Removes the longest string containing only characters in264        <em class="parameter"><code>characters</code></em> (a space by default) from the end of265        <em class="parameter"><code>string</code></em>.266       </p>267       <p>268        <code class="literal">rtrim('testxxzx', 'xyz')</code>269        → <code class="returnvalue">test</code>270       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">271        <a id="id-1.5.8.10.5.2.2.17.1.1.1" class="indexterm"></a>272        <code class="function">substring</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</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>] )273        → <code class="returnvalue">text</code>274       </p>275       <p>276        Extracts the substring of <em class="parameter"><code>string</code></em> starting at277        the <em class="parameter"><code>start</code></em>'th character if that is specified,278        and stopping after <em class="parameter"><code>count</code></em> characters if that is279        specified.  Provide at least one of <em class="parameter"><code>start</code></em>280        and <em class="parameter"><code>count</code></em>.281       </p>282       <p>283        <code class="literal">substring('Thomas' from 2 for 3)</code>284        → <code class="returnvalue">hom</code>285       </p>286       <p>287        <code class="literal">substring('Thomas' from 3)</code>288        → <code class="returnvalue">omas</code>289       </p>290       <p>291        <code class="literal">substring('Thomas' for 2)</code>292        → <code class="returnvalue">Th</code>293       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">294        <code class="function">substring</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code> <code class="literal">FROM</code> <em class="parameter"><code>pattern</code></em> <code class="type">text</code> )295        → <code class="returnvalue">text</code>296       </p>297       <p>298        Extracts the first substring matching POSIX regular expression; see299        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.300       </p>301       <p>302        <code class="literal">substring('Thomas' from '...$')</code>303        → <code class="returnvalue">mas</code>304       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">305        <code class="function">substring</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code> <code class="literal">SIMILAR</code> <em class="parameter"><code>pattern</code></em> <code class="type">text</code> <code class="literal">ESCAPE</code> <em class="parameter"><code>escape</code></em> <code class="type">text</code> )306        → <code class="returnvalue">text</code>307       </p>308       <p class="func_signature">309        <code class="function">substring</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code> <code class="literal">FROM</code> <em class="parameter"><code>pattern</code></em> <code class="type">text</code> <code class="literal">FOR</code> <em class="parameter"><code>escape</code></em> <code class="type">text</code> )310        → <code class="returnvalue">text</code>311       </p>312       <p>313        Extracts the first substring matching <acronym class="acronym">SQL</acronym> regular expression;314        see <a class="xref" href="functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP" title="9.7.2. SIMILAR TO Regular Expressions">Section 9.7.2</a>.  The first form has315        been specified since SQL:2003; the second form was only in SQL:1999316        and should be considered obsolete.317       </p>318       <p>319        <code class="literal">substring('Thomas' similar '%#"o_a#"_' escape '#')</code>320        → <code class="returnvalue">oma</code>321       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">322        <a id="id-1.5.8.10.5.2.2.20.1.1.1" class="indexterm"></a>323        <code class="function">trim</code> ( [<span class="optional"> <code class="literal">LEADING</code> | <code class="literal">TRAILING</code> | <code class="literal">BOTH</code> </span>]324        [<span class="optional"> <em class="parameter"><code>characters</code></em> <code class="type">text</code> </span>] <code class="literal">FROM</code>325        <em class="parameter"><code>string</code></em> <code class="type">text</code> )326        → <code class="returnvalue">text</code>327       </p>328       <p>329        Removes the longest string containing only characters in330        <em class="parameter"><code>characters</code></em> (a space by default) from the331        start, end, or both ends (<code class="literal">BOTH</code> is the default)332        of <em class="parameter"><code>string</code></em>.333       </p>334       <p>335        <code class="literal">trim(both 'xyz' from 'yxTomxx')</code>336        → <code class="returnvalue">Tom</code>337       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">338        <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>]339        <em class="parameter"><code>string</code></em> <code class="type">text</code> [<span class="optional">,340        <em class="parameter"><code>characters</code></em> <code class="type">text</code> </span>] )341        → <code class="returnvalue">text</code>342       </p>343       <p>344        This is a non-standard syntax for <code class="function">trim()</code>.345       </p>346       <p>347        <code class="literal">trim(both from 'yxTomxx', 'xyz')</code>348        → <code class="returnvalue">Tom</code>349       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">350        <a id="id-1.5.8.10.5.2.2.22.1.1.1" class="indexterm"></a>351        <code class="function">upper</code> ( <code class="type">text</code> )352        → <code class="returnvalue">text</code>353       </p>354       <p>355        Converts the string to all upper case, according to the rules of the356        database's locale.357       </p>358       <p>359        <code class="literal">upper('tom')</code>360        → <code class="returnvalue">TOM</code>361       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>362    Additional string manipulation functions and operators are available363    and are listed in <a class="xref" href="functions-string.html#FUNCTIONS-STRING-OTHER" title="Table 9.10. Other String Functions and Operators">Table 9.10</a>.  (Some of364    these are used internally to implement365    the <acronym class="acronym">SQL</acronym>-standard string functions listed in366    <a class="xref" href="functions-string.html#FUNCTIONS-STRING-SQL" title="Table 9.9. SQL String Functions and Operators">Table 9.9</a>.)367    There are also pattern-matching operators, which are described in368    <a class="xref" href="functions-matching.html" title="9.7. Pattern Matching">Section 9.7</a>, and operators for full-text369    search, which are described in <a class="xref" href="textsearch.html" title="Chapter 12. Full Text Search">Chapter 12</a>.370   </p><div class="table" id="FUNCTIONS-STRING-OTHER"><p class="title"><strong>Table 9.10. Other String Functions and Operators</strong></p><div class="table-contents"><table class="table" summary="Other String Functions and Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">371        Function/Operator372       </p>373       <p>374        Description375       </p>376       <p>377        Example(s)378       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">379        <a id="id-1.5.8.10.7.2.2.1.1.1.1" class="indexterm"></a>380        <code class="type">text</code> <code class="literal">^@</code> <code class="type">text</code>381        → <code class="returnvalue">boolean</code>382       </p>383       <p>384        Returns true if the first string starts with the second string385        (equivalent to the <code class="function">starts_with()</code> function).386       </p>387       <p>388        <code class="literal">'alphabet' ^@ 'alph'</code>389        → <code class="returnvalue">t</code>390       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">391        <a id="id-1.5.8.10.7.2.2.2.1.1.1" class="indexterm"></a>392        <code class="function">ascii</code> ( <code class="type">text</code> )393        → <code class="returnvalue">integer</code>394       </p>395       <p>396        Returns the numeric code of the first character of the argument.397        In <acronym class="acronym">UTF8</acronym> encoding, returns the Unicode code point398        of the character.  In other multibyte encodings, the argument must399        be an <acronym class="acronym">ASCII</acronym> character.400       </p>401       <p>402        <code class="literal">ascii('x')</code>403        → <code class="returnvalue">120</code>404       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">405        <a id="id-1.5.8.10.7.2.2.3.1.1.1" class="indexterm"></a>406        <code class="function">chr</code> ( <code class="type">integer</code> )407        → <code class="returnvalue">text</code>408       </p>409       <p>410        Returns the character with the given code. In <acronym class="acronym">UTF8</acronym>411        encoding the argument is treated as a Unicode code point. In other412        multibyte encodings the argument must designate413        an <acronym class="acronym">ASCII</acronym> character.  <code class="literal">chr(0)</code> is414        disallowed because text data types cannot store that character.415      </p>416      <p>417        <code class="literal">chr(65)</code>418        → <code class="returnvalue">A</code>419       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">420        <a id="id-1.5.8.10.7.2.2.4.1.1.1" class="indexterm"></a>421        <code class="function">concat</code> ( <em class="parameter"><code>val1</code></em> <code class="type">"any"</code>422         [, <em class="parameter"><code>val2</code></em> <code class="type">"any"</code> [, ...] ] )423        → <code class="returnvalue">text</code>424       </p>425       <p>426        Concatenates the text representations of all the arguments.427        NULL arguments are ignored.428       </p>429       <p>430        <code class="literal">concat('abcde', 2, NULL, 22)</code>431        → <code class="returnvalue">abcde222</code>432       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">433        <a id="id-1.5.8.10.7.2.2.5.1.1.1" class="indexterm"></a>434        <code class="function">concat_ws</code> ( <em class="parameter"><code>sep</code></em> <code class="type">text</code>,435        <em class="parameter"><code>val1</code></em> <code class="type">"any"</code>436        [, <em class="parameter"><code>val2</code></em> <code class="type">"any"</code> [, ...] ] )437        → <code class="returnvalue">text</code>438       </p>439       <p>440        Concatenates all but the first argument, with separators. The first441        argument is used as the separator string, and should not be NULL.442        Other NULL arguments are ignored.443       </p>444       <p>445        <code class="literal">concat_ws(',', 'abcde', 2, NULL, 22)</code>446        → <code class="returnvalue">abcde,2,22</code>447       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">448        <a id="id-1.5.8.10.7.2.2.6.1.1.1" class="indexterm"></a>449        <code class="function">format</code> ( <em class="parameter"><code>formatstr</code></em> <code class="type">text</code>450        [, <em class="parameter"><code>formatarg</code></em> <code class="type">"any"</code> [, ...] ] )451        → <code class="returnvalue">text</code>452       </p>453       <p>454         Formats arguments according to a format string;455         see <a class="xref" href="functions-string.html#FUNCTIONS-STRING-FORMAT" title="9.4.1. format">Section 9.4.1</a>.456         This function is similar to the C function <code class="function">sprintf</code>.457       </p>458       <p>459        <code class="literal">format('Hello %s, %1$s', 'World')</code>460        → <code class="returnvalue">Hello World, World</code>461       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">462        <a id="id-1.5.8.10.7.2.2.7.1.1.1" class="indexterm"></a>463        <code class="function">initcap</code> ( <code class="type">text</code> )464        → <code class="returnvalue">text</code>465       </p>466       <p>467        Converts the first letter of each word to upper case and the468        rest to lower case. Words are sequences of alphanumeric469        characters separated by non-alphanumeric characters.470       </p>471       <p>472        <code class="literal">initcap('hi THOMAS')</code>473        → <code class="returnvalue">Hi Thomas</code>474       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">475        <a id="id-1.5.8.10.7.2.2.8.1.1.1" class="indexterm"></a>476        <code class="function">left</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,477        <em class="parameter"><code>n</code></em> <code class="type">integer</code> )478        → <code class="returnvalue">text</code>479       </p>480       <p>481        Returns first <em class="parameter"><code>n</code></em> characters in the482        string, or when <em class="parameter"><code>n</code></em> is negative, returns483        all but last |<em class="parameter"><code>n</code></em>| characters.484       </p>485       <p>486        <code class="literal">left('abcde', 2)</code>487        → <code class="returnvalue">ab</code>488       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">489        <a id="id-1.5.8.10.7.2.2.9.1.1.1" class="indexterm"></a>490        <code class="function">length</code> ( <code class="type">text</code> )491        → <code class="returnvalue">integer</code>492       </p>493       <p>494        Returns the number of characters in the string.495       </p>496       <p>497        <code class="literal">length('jose')</code>498        → <code class="returnvalue">4</code>499       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">500        <a id="id-1.5.8.10.7.2.2.10.1.1.1" class="indexterm"></a>501        <code class="function">md5</code> ( <code class="type">text</code> )502        → <code class="returnvalue">text</code>503       </p>504       <p>505        Computes the MD5 <a class="link" href="functions-binarystring.html#FUNCTIONS-HASH-NOTE">hash</a> of506        the argument, with the result written in hexadecimal.507       </p>508       <p>509        <code class="literal">md5('abc')</code>510        → <code class="returnvalue">900150983cd24fb0​d6963f7d28e17f72</code>511       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">512        <a id="id-1.5.8.10.7.2.2.11.1.1.1" class="indexterm"></a>513        <code class="function">parse_ident</code> ( <em class="parameter"><code>qualified_identifier</code></em> <code class="type">text</code>514        [, <em class="parameter"><code>strict_mode</code></em> <code class="type">boolean</code> <code class="literal">DEFAULT</code> <code class="literal">true</code> ] )515        → <code class="returnvalue">text[]</code>516       </p>517       <p>518        Splits <em class="parameter"><code>qualified_identifier</code></em> into an array of519        identifiers, removing any quoting of individual identifiers.  By520        default, extra characters after the last identifier are considered an521        error; but if the second parameter is <code class="literal">false</code>, then such522        extra characters are ignored. (This behavior is useful for parsing523        names for objects like functions.) Note that this function does not524        truncate over-length identifiers. If you want truncation you can cast525        the result to <code class="type">name[]</code>.526       </p>527       <p>528        <code class="literal">parse_ident('"SomeSchema".someTable')</code>529        → <code class="returnvalue">{SomeSchema,sometable}</code>530       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">531        <a id="id-1.5.8.10.7.2.2.12.1.1.1" class="indexterm"></a>532        <code class="function">pg_client_encoding</code> ( )533        → <code class="returnvalue">name</code>534       </p>535       <p>536        Returns current client encoding name.537       </p>538       <p>539        <code class="literal">pg_client_encoding()</code>540        → <code class="returnvalue">UTF8</code>541       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">542        <a id="id-1.5.8.10.7.2.2.13.1.1.1" class="indexterm"></a>543        <code class="function">quote_ident</code> ( <code class="type">text</code> )544        → <code class="returnvalue">text</code>545       </p>546       <p>547        Returns the given string suitably quoted to be used as an identifier548        in an <acronym class="acronym">SQL</acronym> statement string.549        Quotes are added only if necessary (i.e., if the string contains550        non-identifier characters or would be case-folded).551        Embedded quotes are properly doubled.552        See also <a class="xref" href="plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" title="Example 43.1. Quoting Values in Dynamic Queries">Example 43.1</a>.553       </p>554       <p>555        <code class="literal">quote_ident('Foo bar')</code>556        → <code class="returnvalue">"Foo bar"</code>557       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">558        <a id="id-1.5.8.10.7.2.2.14.1.1.1" class="indexterm"></a>559        <code class="function">quote_literal</code> ( <code class="type">text</code> )560        → <code class="returnvalue">text</code>561       </p>562       <p>563        Returns the given string suitably quoted to be used as a string literal564        in an <acronym class="acronym">SQL</acronym> statement string.565        Embedded single-quotes and backslashes are properly doubled.566        Note that <code class="function">quote_literal</code> returns null on null567        input; if the argument might be null,568        <code class="function">quote_nullable</code> is often more suitable.569        See also <a class="xref" href="plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" title="Example 43.1. Quoting Values in Dynamic Queries">Example 43.1</a>.570       </p>571       <p>572        <code class="literal">quote_literal(E'O\'Reilly')</code>573        → <code class="returnvalue">'O''Reilly'</code>574       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">575        <code class="function">quote_literal</code> ( <code class="type">anyelement</code> )576        → <code class="returnvalue">text</code>577       </p>578       <p>579        Converts the given value to text and then quotes it as a literal.580        Embedded single-quotes and backslashes are properly doubled.581       </p>582       <p>583        <code class="literal">quote_literal(42.5)</code>584        → <code class="returnvalue">'42.5'</code>585       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">586        <a id="id-1.5.8.10.7.2.2.16.1.1.1" class="indexterm"></a>587        <code class="function">quote_nullable</code> ( <code class="type">text</code> )588        → <code class="returnvalue">text</code>589       </p>590       <p>591        Returns the given string suitably quoted to be used as a string literal592        in an <acronym class="acronym">SQL</acronym> statement string; or, if the argument593        is null, returns <code class="literal">NULL</code>.594        Embedded single-quotes and backslashes are properly doubled.595        See also <a class="xref" href="plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" title="Example 43.1. Quoting Values in Dynamic Queries">Example 43.1</a>.596       </p>597       <p>598        <code class="literal">quote_nullable(NULL)</code>599        → <code class="returnvalue">NULL</code>600       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">601        <code class="function">quote_nullable</code> ( <code class="type">anyelement</code> )602        → <code class="returnvalue">text</code>603       </p>604       <p>605        Converts the given value to text and then quotes it as a literal;606        or, if the argument is null, returns <code class="literal">NULL</code>.607        Embedded single-quotes and backslashes are properly doubled.608       </p>609       <p>610        <code class="literal">quote_nullable(42.5)</code>611        → <code class="returnvalue">'42.5'</code>612       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">613        <a id="id-1.5.8.10.7.2.2.18.1.1.1" class="indexterm"></a>614        <code class="function">regexp_count</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code>615         [, <em class="parameter"><code>start</code></em> <code class="type">integer</code>616         [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] ] )617        → <code class="returnvalue">integer</code>618       </p>619       <p>620        Returns the number of times the POSIX regular621        expression <em class="parameter"><code>pattern</code></em> matches in622        the <em class="parameter"><code>string</code></em>; see623        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.624       </p>625       <p>626        <code class="literal">regexp_count('123456789012', '\d\d\d', 2)</code>627        → <code class="returnvalue">3</code>628       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">629        <a id="id-1.5.8.10.7.2.2.19.1.1.1" class="indexterm"></a>630        <code class="function">regexp_instr</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code>631         [, <em class="parameter"><code>start</code></em> <code class="type">integer</code>632         [, <em class="parameter"><code>N</code></em> <code class="type">integer</code>633         [, <em class="parameter"><code>endoption</code></em> <code class="type">integer</code>634         [, <em class="parameter"><code>flags</code></em> <code class="type">text</code>635         [, <em class="parameter"><code>subexpr</code></em> <code class="type">integer</code> ] ] ] ] ] )636        → <code class="returnvalue">integer</code>637       </p>638       <p>639        Returns the position within <em class="parameter"><code>string</code></em> where640        the <em class="parameter"><code>N</code></em>'th match of the POSIX regular641        expression <em class="parameter"><code>pattern</code></em> occurs, or zero if there is642        no such match; see <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.643       </p>644       <p>645        <code class="literal">regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i')</code>646        → <code class="returnvalue">3</code>647       </p>648       <p>649        <code class="literal">regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i', 2)</code>650        → <code class="returnvalue">5</code>651       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">652        <a id="id-1.5.8.10.7.2.2.20.1.1.1" class="indexterm"></a>653        <code class="function">regexp_like</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code>654         [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] )655        → <code class="returnvalue">boolean</code>656       </p>657       <p>658        Checks whether a match of the POSIX regular659        expression <em class="parameter"><code>pattern</code></em> occurs660        within <em class="parameter"><code>string</code></em>; see661        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.662       </p>663       <p>664        <code class="literal">regexp_like('Hello World', 'world$', 'i')</code>665        → <code class="returnvalue">t</code>666       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">667        <a id="id-1.5.8.10.7.2.2.21.1.1.1" class="indexterm"></a>668        <code class="function">regexp_match</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code> [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] )669        → <code class="returnvalue">text[]</code>670       </p>671       <p>672        Returns substrings within the first match of the POSIX regular673        expression <em class="parameter"><code>pattern</code></em> to674        the <em class="parameter"><code>string</code></em>; see675        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.676       </p>677       <p>678        <code class="literal">regexp_match('foobarbequebaz', '(bar)(beque)')</code>679        → <code class="returnvalue">{bar,beque}</code>680       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">681        <a id="id-1.5.8.10.7.2.2.22.1.1.1" class="indexterm"></a>682        <code class="function">regexp_matches</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code> [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] )683        → <code class="returnvalue">setof text[]</code>684       </p>685       <p>686        Returns substrings within the first match of the POSIX regular687        expression <em class="parameter"><code>pattern</code></em> to688        the <em class="parameter"><code>string</code></em>, or substrings within all689        such matches if the <code class="literal">g</code> flag is used;690        see <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.691       </p>692       <p>693        <code class="literal">regexp_matches('foobarbequebaz', 'ba.', 'g')</code>694        → <code class="returnvalue"></code>695</p><pre class="programlisting">696 {bar}697 {baz}698</pre><p>699       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">700        <a id="id-1.5.8.10.7.2.2.23.1.1.1" class="indexterm"></a>701        <code class="function">regexp_replace</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code>, <em class="parameter"><code>replacement</code></em> <code class="type">text</code>702         [, <em class="parameter"><code>start</code></em> <code class="type">integer</code> ]703         [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] )704        → <code class="returnvalue">text</code>705       </p>706       <p>707        Replaces the substring that is the first match to the POSIX708        regular expression <em class="parameter"><code>pattern</code></em>, or all such709        matches if the <code class="literal">g</code> flag is used; see710        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.711       </p>712       <p>713        <code class="literal">regexp_replace('Thomas', '.[mN]a.', 'M')</code>714        → <code class="returnvalue">ThM</code>715       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">716        <code class="function">regexp_replace</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code>, <em class="parameter"><code>replacement</code></em> <code class="type">text</code>,717         <em class="parameter"><code>start</code></em> <code class="type">integer</code>,718         <em class="parameter"><code>N</code></em> <code class="type">integer</code>719         [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] )720        → <code class="returnvalue">text</code>721       </p>722       <p>723        Replaces the substring that is the <em class="parameter"><code>N</code></em>'th724        match to the POSIX regular expression <em class="parameter"><code>pattern</code></em>,725        or all such matches if <em class="parameter"><code>N</code></em> is zero; see726        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.727       </p>728       <p>729        <code class="literal">regexp_replace('Thomas', '.', 'X', 3, 2)</code>730        → <code class="returnvalue">ThoXas</code>731       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">732        <a id="id-1.5.8.10.7.2.2.25.1.1.1" class="indexterm"></a>733        <code class="function">regexp_split_to_array</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code> [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] )734        → <code class="returnvalue">text[]</code>735       </p>736       <p>737        Splits <em class="parameter"><code>string</code></em> using a POSIX regular738        expression as the delimiter, producing an array of results; see739        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.740       </p>741       <p>742        <code class="literal">regexp_split_to_array('hello world', '\s+')</code>743        → <code class="returnvalue">{hello,world}</code>744       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">745        <a id="id-1.5.8.10.7.2.2.26.1.1.1" class="indexterm"></a>746        <code class="function">regexp_split_to_table</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code> [, <em class="parameter"><code>flags</code></em> <code class="type">text</code> ] )747        → <code class="returnvalue">setof text</code>748       </p>749       <p>750        Splits <em class="parameter"><code>string</code></em> using a POSIX regular751        expression as the delimiter, producing a set of results; see752        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.753       </p>754       <p>755        <code class="literal">regexp_split_to_table('hello world', '\s+')</code>756        → <code class="returnvalue"></code>757</p><pre class="programlisting">758 hello759 world760</pre><p>761       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">762        <a id="id-1.5.8.10.7.2.2.27.1.1.1" class="indexterm"></a>763        <code class="function">regexp_substr</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>pattern</code></em> <code class="type">text</code>764         [, <em class="parameter"><code>start</code></em> <code class="type">integer</code>765         [, <em class="parameter"><code>N</code></em> <code class="type">integer</code>766         [, <em class="parameter"><code>flags</code></em> <code class="type">text</code>767         [, <em class="parameter"><code>subexpr</code></em> <code class="type">integer</code> ] ] ] ] )768        → <code class="returnvalue">text</code>769       </p>770       <p>771        Returns the substring within <em class="parameter"><code>string</code></em> that772        matches the <em class="parameter"><code>N</code></em>'th occurrence of the POSIX773        regular expression <em class="parameter"><code>pattern</code></em>,774        or <code class="literal">NULL</code> if there is no such match; see775        <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.776       </p>777       <p>778        <code class="literal">regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i')</code>779        → <code class="returnvalue">CDEF</code>780       </p>781       <p>782        <code class="literal">regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i', 2)</code>783        → <code class="returnvalue">EF</code>784       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">785        <a id="id-1.5.8.10.7.2.2.28.1.1.1" class="indexterm"></a>786        <code class="function">repeat</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>number</code></em> <code class="type">integer</code> )787        → <code class="returnvalue">text</code>788       </p>789       <p>790        Repeats <em class="parameter"><code>string</code></em> the specified791        <em class="parameter"><code>number</code></em> of times.792       </p>793       <p>794        <code class="literal">repeat('Pg', 4)</code>795        → <code class="returnvalue">PgPgPgPg</code>796       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">797        <a id="id-1.5.8.10.7.2.2.29.1.1.1" class="indexterm"></a>798        <code class="function">replace</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,799        <em class="parameter"><code>from</code></em> <code class="type">text</code>,800        <em class="parameter"><code>to</code></em> <code class="type">text</code> )801        → <code class="returnvalue">text</code>802       </p>803       <p>804        Replaces all occurrences in <em class="parameter"><code>string</code></em> of805        substring <em class="parameter"><code>from</code></em> with806        substring <em class="parameter"><code>to</code></em>.807       </p>808       <p>809        <code class="literal">replace('abcdefabcdef', 'cd', 'XX')</code>810        → <code class="returnvalue">abXXefabXXef</code>811       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">812        <a id="id-1.5.8.10.7.2.2.30.1.1.1" class="indexterm"></a>813        <code class="function">reverse</code> ( <code class="type">text</code> )814        → <code class="returnvalue">text</code>815       </p>816       <p>817        Reverses the order of the characters in the string.818       </p>819       <p>820        <code class="literal">reverse('abcde')</code>821        → <code class="returnvalue">edcba</code>822       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">823        <a id="id-1.5.8.10.7.2.2.31.1.1.1" class="indexterm"></a>824        <code class="function">right</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,825         <em class="parameter"><code>n</code></em> <code class="type">integer</code> )826        → <code class="returnvalue">text</code>827       </p>828       <p>829        Returns last <em class="parameter"><code>n</code></em> characters in the string,830        or when <em class="parameter"><code>n</code></em> is negative, returns all but831        first |<em class="parameter"><code>n</code></em>| characters.832       </p>833       <p>834        <code class="literal">right('abcde', 2)</code>835        → <code class="returnvalue">de</code>836       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">837        <a id="id-1.5.8.10.7.2.2.32.1.1.1" class="indexterm"></a>838        <code class="function">split_part</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,839        <em class="parameter"><code>delimiter</code></em> <code class="type">text</code>,840        <em class="parameter"><code>n</code></em> <code class="type">integer</code> )841        → <code class="returnvalue">text</code>842       </p>843       <p>844        Splits <em class="parameter"><code>string</code></em> at occurrences845        of <em class="parameter"><code>delimiter</code></em> and returns846        the <em class="parameter"><code>n</code></em>'th field (counting from one),847        or when <em class="parameter"><code>n</code></em> is negative, returns848        the |<em class="parameter"><code>n</code></em>|'th-from-last field.849       </p>850       <p>851        <code class="literal">split_part('abc~@~def~@~ghi', '~@~', 2)</code>852        → <code class="returnvalue">def</code>853       </p>854       <p>855        <code class="literal">split_part('abc,def,ghi,jkl', ',', -2)</code>856        → <code class="returnvalue">ghi</code>857       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">858        <a id="id-1.5.8.10.7.2.2.33.1.1.1" class="indexterm"></a>859        <code class="function">starts_with</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>prefix</code></em> <code class="type">text</code> )860        → <code class="returnvalue">boolean</code>861       </p>862       <p>863        Returns true if <em class="parameter"><code>string</code></em> starts864        with <em class="parameter"><code>prefix</code></em>.865       </p>866       <p>867        <code class="literal">starts_with('alphabet', 'alph')</code>868        → <code class="returnvalue">t</code>869       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">870        <a id="FUNCTION-STRING-TO-ARRAY" class="indexterm"></a>871        <code class="function">string_to_array</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>delimiter</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>null_string</code></em> <code class="type">text</code> </span>] )872        → <code class="returnvalue">text[]</code>873       </p>874       <p>875        Splits the <em class="parameter"><code>string</code></em> at occurrences876        of <em class="parameter"><code>delimiter</code></em> and forms the resulting fields877        into a <code class="type">text</code> array.878        If <em class="parameter"><code>delimiter</code></em> is <code class="literal">NULL</code>,879        each character in the <em class="parameter"><code>string</code></em> will become a880        separate element in the array.881        If <em class="parameter"><code>delimiter</code></em> is an empty string, then882        the <em class="parameter"><code>string</code></em> is treated as a single field.883        If <em class="parameter"><code>null_string</code></em> is supplied and is884        not <code class="literal">NULL</code>, fields matching that string are885        replaced by <code class="literal">NULL</code>.886        See also <a class="link" href="functions-array.html#FUNCTION-ARRAY-TO-STRING"><code class="function">array_to_string</code></a>.887       </p>888       <p>889        <code class="literal">string_to_array('xx~~yy~~zz', '~~', 'yy')</code>890        → <code class="returnvalue">{xx,NULL,zz}</code>891       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">892        <a id="id-1.5.8.10.7.2.2.35.1.1.1" class="indexterm"></a>893        <code class="function">string_to_table</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>delimiter</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>null_string</code></em> <code class="type">text</code> </span>] )894        → <code class="returnvalue">setof text</code>895       </p>896       <p>897        Splits the <em class="parameter"><code>string</code></em> at occurrences898        of <em class="parameter"><code>delimiter</code></em> and returns the resulting fields899        as a set of <code class="type">text</code> rows.900        If <em class="parameter"><code>delimiter</code></em> is <code class="literal">NULL</code>,901        each character in the <em class="parameter"><code>string</code></em> will become a902        separate row of the result.903        If <em class="parameter"><code>delimiter</code></em> is an empty string, then904        the <em class="parameter"><code>string</code></em> is treated as a single field.905        If <em class="parameter"><code>null_string</code></em> is supplied and is906        not <code class="literal">NULL</code>, fields matching that string are907        replaced by <code class="literal">NULL</code>.908       </p>909       <p>910        <code class="literal">string_to_table('xx~^~yy~^~zz', '~^~', 'yy')</code>911        → <code class="returnvalue"></code>912</p><pre class="programlisting">913 xx914 NULL915 zz916</pre><p>917       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">918        <a id="id-1.5.8.10.7.2.2.36.1.1.1" class="indexterm"></a>919        <code class="function">strpos</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>substring</code></em> <code class="type">text</code> )920        → <code class="returnvalue">integer</code>921       </p>922       <p>923        Returns first starting index of the specified <em class="parameter"><code>substring</code></em>924        within <em class="parameter"><code>string</code></em>, or zero if it's not present.925        (Same as <code class="literal">position(<em class="parameter"><code>substring</code></em> in926        <em class="parameter"><code>string</code></em>)</code>, but note the reversed927        argument order.)928       </p>929       <p>930        <code class="literal">strpos('high', 'ig')</code>931        → <code class="returnvalue">2</code>932       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">933        <a id="id-1.5.8.10.7.2.2.37.1.1.1" class="indexterm"></a>934        <code class="function">substr</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</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>] )935        → <code class="returnvalue">text</code>936       </p>937       <p>938        Extracts the substring of <em class="parameter"><code>string</code></em> starting at939        the <em class="parameter"><code>start</code></em>'th character,940        and extending for <em class="parameter"><code>count</code></em> characters if that is941        specified.  (Same942        as <code class="literal">substring(<em class="parameter"><code>string</code></em>943        from <em class="parameter"><code>start</code></em>944        for <em class="parameter"><code>count</code></em>)</code>.)945       </p>946       <p>947        <code class="literal">substr('alphabet', 3)</code>948        → <code class="returnvalue">phabet</code>949       </p>950       <p>951        <code class="literal">substr('alphabet', 3, 2)</code>952        → <code class="returnvalue">ph</code>953       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">954        <a id="id-1.5.8.10.7.2.2.38.1.1.1" class="indexterm"></a>955        <code class="function">to_ascii</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code> )956        → <code class="returnvalue">text</code>957       </p>958       <p class="func_signature">959        <code class="function">to_ascii</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,960        <em class="parameter"><code>encoding</code></em> <code class="type">name</code> )961        → <code class="returnvalue">text</code>962       </p>963       <p class="func_signature">964        <code class="function">to_ascii</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,965        <em class="parameter"><code>encoding</code></em> <code class="type">integer</code> )966        → <code class="returnvalue">text</code>967       </p>968       <p>969        Converts <em class="parameter"><code>string</code></em> to <acronym class="acronym">ASCII</acronym>970        from another encoding, which may be identified by name or number.971        If <em class="parameter"><code>encoding</code></em> is omitted the database encoding972        is assumed (which in practice is the only useful case).973        The conversion consists primarily of dropping accents.974        Conversion is only supported975        from <code class="literal">LATIN1</code>, <code class="literal">LATIN2</code>,976        <code class="literal">LATIN9</code>, and <code class="literal">WIN1250</code> encodings.977        (See the <a class="xref" href="unaccent.html" title="F.48. unaccent — a text search dictionary which removes diacritics">unaccent</a> module for another, more flexible978        solution.)979       </p>980       <p>981        <code class="literal">to_ascii('Karél')</code>982        → <code class="returnvalue">Karel</code>983       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">984        <a id="id-1.5.8.10.7.2.2.39.1.1.1" class="indexterm"></a>985        <code class="function">to_hex</code> ( <code class="type">integer</code> )986        → <code class="returnvalue">text</code>987       </p>988       <p class="func_signature">989        <code class="function">to_hex</code> ( <code class="type">bigint</code> )990        → <code class="returnvalue">text</code>991       </p>992       <p>993        Converts the number to its equivalent hexadecimal representation.994       </p>995       <p>996        <code class="literal">to_hex(2147483647)</code>997        → <code class="returnvalue">7fffffff</code>998       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">999        <a id="id-1.5.8.10.7.2.2.40.1.1.1" class="indexterm"></a>1000        <code class="function">translate</code> ( <em class="parameter"><code>string</code></em> <code class="type">text</code>,1001        <em class="parameter"><code>from</code></em> <code class="type">text</code>,1002        <em class="parameter"><code>to</code></em> <code class="type">text</code> )1003        → <code class="returnvalue">text</code>1004       </p>1005       <p>1006        Replaces each character in <em class="parameter"><code>string</code></em> that1007        matches a character in the <em class="parameter"><code>from</code></em> set with the1008        corresponding character in the <em class="parameter"><code>to</code></em>1009        set. If <em class="parameter"><code>from</code></em> is longer than1010        <em class="parameter"><code>to</code></em>, occurrences of the extra characters in1011        <em class="parameter"><code>from</code></em> are deleted.1012       </p>1013       <p>1014        <code class="literal">translate('12345', '143', 'ax')</code>1015        → <code class="returnvalue">a2x5</code>1016       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1017        <a id="id-1.5.8.10.7.2.2.41.1.1.1" class="indexterm"></a>1018        <code class="function">unistr</code> ( <code class="type">text</code> )1019        → <code class="returnvalue">text</code>1020       </p>1021       <p>1022        Evaluate escaped Unicode characters in the argument.  Unicode characters1023        can be specified as1024        <code class="literal">\<em class="replaceable"><code>XXXX</code></em></code> (4 hexadecimal1025        digits), <code class="literal">\+<em class="replaceable"><code>XXXXXX</code></em></code> (61026        hexadecimal digits),1027        <code class="literal">\u<em class="replaceable"><code>XXXX</code></em></code> (4 hexadecimal1028        digits), or <code class="literal">\U<em class="replaceable"><code>XXXXXXXX</code></em></code>1029        (8 hexadecimal digits).  To specify a backslash, write two1030        backslashes.  All other characters are taken literally.1031       </p>1032 1033       <p>1034        If the server encoding is not UTF-8, the Unicode code point identified1035        by one of these escape sequences is converted to the actual server1036        encoding; an error is reported if that's not possible.1037       </p>1038 1039       <p>1040        This function provides a (non-standard) alternative to string1041        constants with Unicode escapes (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE" title="4.1.2.3. String Constants with Unicode Escapes">Section 4.1.2.3</a>).1042       </p>1043 1044       <p>1045        <code class="literal">unistr('d\0061t\+000061')</code>1046        → <code class="returnvalue">data</code>1047       </p>1048       <p>1049        <code class="literal">unistr('d\u0061t\U00000061')</code>1050        → <code class="returnvalue">data</code>1051       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>1052    The <code class="function">concat</code>, <code class="function">concat_ws</code> and1053    <code class="function">format</code> functions are variadic, so it is possible to1054    pass the values to be concatenated or formatted as an array marked with1055    the <code class="literal">VARIADIC</code> keyword (see <a class="xref" href="xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS" title="38.5.6. SQL Functions with Variable Numbers of Arguments">Section 38.5.6</a>).  The array's elements are1056    treated as if they were separate ordinary arguments to the function.1057    If the variadic array argument is NULL, <code class="function">concat</code>1058    and <code class="function">concat_ws</code> return NULL, but1059    <code class="function">format</code> treats a NULL as a zero-element array.1060   </p><p>1061    See also the aggregate function <code class="function">string_agg</code> in1062    <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a>, and the functions for1063    converting between strings and the <code class="type">bytea</code> type in1064    <a class="xref" href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS" title="Table 9.13. Text/Binary String Conversion Functions">Table 9.13</a>.1065   </p><div class="sect2" id="FUNCTIONS-STRING-FORMAT"><div class="titlepage"><div><div><h3 class="title">9.4.1. <code class="function">format</code> <a href="#FUNCTIONS-STRING-FORMAT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.10.10.2" class="indexterm"></a><p>1066     The function <code class="function">format</code> produces output formatted according to1067     a format string, in a style similar to the C function1068     <code class="function">sprintf</code>.1069    </p><p>1070</p><pre class="synopsis">1071<code class="function">format</code>(<em class="parameter"><code>formatstr</code></em> <code class="type">text</code> [, <em class="parameter"><code>formatarg</code></em> <code class="type">"any"</code> [, ...] ])1072</pre><p>1073     <em class="parameter"><code>formatstr</code></em> is a format string that specifies how the1074     result should be formatted.  Text in the format string is copied1075     directly to the result, except where <em class="firstterm">format specifiers</em> are1076     used.  Format specifiers act as placeholders in the string, defining how1077     subsequent function arguments should be formatted and inserted into the1078     result.  Each <em class="parameter"><code>formatarg</code></em> argument is converted to text1079     according to the usual output rules for its data type, and then formatted1080     and inserted into the result string according to the format specifier(s).1081    </p><p>1082     Format specifiers are introduced by a <code class="literal">%</code> character and have1083     the form1084</p><pre class="synopsis">1085%[<em class="parameter"><code>position</code></em>][<em class="parameter"><code>flags</code></em>][<em class="parameter"><code>width</code></em>]<em class="parameter"><code>type</code></em>1086</pre><p>1087     where the component fields are:1088 1089     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="parameter"><code>position</code></em> (optional)</span></dt><dd><p>1090         A string of the form <code class="literal"><em class="parameter"><code>n</code></em>$</code> where1091         <em class="parameter"><code>n</code></em> is the index of the argument to print.1092         Index 1 means the first argument after1093         <em class="parameter"><code>formatstr</code></em>.  If the <em class="parameter"><code>position</code></em> is1094         omitted, the default is to use the next argument in sequence.1095        </p></dd><dt><span class="term"><em class="parameter"><code>flags</code></em> (optional)</span></dt><dd><p>1096         Additional options controlling how the format specifier's output is1097         formatted.  Currently the only supported flag is a minus sign1098         (<code class="literal">-</code>) which will cause the format specifier's output to be1099         left-justified.  This has no effect unless the <em class="parameter"><code>width</code></em>1100         field is also specified.1101        </p></dd><dt><span class="term"><em class="parameter"><code>width</code></em> (optional)</span></dt><dd><p>1102         Specifies the <span class="emphasis"><em>minimum</em></span> number of characters to use to1103         display the format specifier's output.  The output is padded on the1104         left or right (depending on the <code class="literal">-</code> flag) with spaces as1105         needed to fill the width.  A too-small width does not cause1106         truncation of the output, but is simply ignored.  The width may be1107         specified using any of the following: a positive integer; an1108         asterisk (<code class="literal">*</code>) to use the next function argument as the1109         width; or a string of the form <code class="literal">*<em class="parameter"><code>n</code></em>$</code> to1110         use the <em class="parameter"><code>n</code></em>th function argument as the width.1111        </p><p>1112         If the width comes from a function argument, that argument is1113         consumed before the argument that is used for the format specifier's1114         value.  If the width argument is negative, the result is left1115         aligned (as if the <code class="literal">-</code> flag had been specified) within a1116         field of length <code class="function">abs</code>(<em class="parameter"><code>width</code></em>).1117        </p></dd><dt><span class="term"><em class="parameter"><code>type</code></em> (required)</span></dt><dd><p>1118         The type of format conversion to use to produce the format1119         specifier's output.  The following types are supported:1120         </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>1121            <code class="literal">s</code> formats the argument value as a simple1122            string.  A null value is treated as an empty string.1123           </p></li><li class="listitem"><p>1124            <code class="literal">I</code> treats the argument value as an SQL1125            identifier, double-quoting it if necessary.1126            It is an error for the value to be null (equivalent to1127            <code class="function">quote_ident</code>).1128           </p></li><li class="listitem"><p>1129            <code class="literal">L</code> quotes the argument value as an SQL literal.1130            A null value is displayed as the string <code class="literal">NULL</code>, without1131            quotes (equivalent to <code class="function">quote_nullable</code>).1132           </p></li></ul></div><p>1133        </p></dd></dl></div><p>1134    </p><p>1135     In addition to the format specifiers described above, the special sequence1136     <code class="literal">%%</code> may be used to output a literal <code class="literal">%</code> character.1137    </p><p>1138     Here are some examples of the basic format conversions:1139 1140</p><pre class="screen">1141SELECT format('Hello %s', 'World');1142<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Hello World</code>1143 1144SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');1145<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Testing one, two, three, %</code>1146 1147SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');1148<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">INSERT INTO "Foo bar" VALUES('O''Reilly')</code>1149 1150SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');1151<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">INSERT INTO locations VALUES('C:\Program Files')</code>1152</pre><p>1153    </p><p>1154     Here are examples using <em class="parameter"><code>width</code></em> fields1155     and the <code class="literal">-</code> flag:1156 1157</p><pre class="screen">1158SELECT format('|%10s|', 'foo');1159<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       foo|</code>1160 1161SELECT format('|%-10s|', 'foo');1162<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>1163 1164SELECT format('|%*s|', 10, 'foo');1165<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       foo|</code>1166 1167SELECT format('|%*s|', -10, 'foo');1168<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>1169 1170SELECT format('|%-*s|', 10, 'foo');1171<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>1172 1173SELECT format('|%-*s|', -10, 'foo');1174<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>1175</pre><p>1176    </p><p>1177     These examples show use of <em class="parameter"><code>position</code></em> fields:1178 1179</p><pre class="screen">1180SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');1181<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Testing three, two, one</code>1182 1183SELECT format('|%*2$s|', 'foo', 10, 'bar');1184<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       bar|</code>1185 1186SELECT format('|%1$*2$s|', 'foo', 10, 'bar');1187<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       foo|</code>1188</pre><p>1189    </p><p>1190     Unlike the standard C function <code class="function">sprintf</code>,1191     <span class="productname">PostgreSQL</span>'s <code class="function">format</code> function allows format1192     specifiers with and without <em class="parameter"><code>position</code></em> fields to be mixed1193     in the same format string.  A format specifier without a1194     <em class="parameter"><code>position</code></em> field always uses the next argument after the1195     last argument consumed.1196     In addition, the <code class="function">format</code> function does not require all1197     function arguments to be used in the format string.1198     For example:1199 1200</p><pre class="screen">

Showing the first 1,200 of 1208 lines. Download the file for the rest.

codekingpro/portable-devtools · Team Ai