codekingpro/portable-devtools
115k
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&'\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&'\0061\0308bc', NFC)</code>185 → <code class="returnvalue">U&'\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">900150983cd24fb0d6963f7d28e17f72</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">