Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
关注 1
点赞 0
百科 捐赠
gooderp18绿色标准版
您最多选择25个主题 主题必须以字母或数字开头，可以包含连字符 (-)，并且长度不得超过35个字符
分支: master
分支列表 标签列表
${ item.name }
创建分支 ${ searchTerm }
从 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/functions-string.html

591 行
76KB
原始文件 永久链接 Blame 文件历史 

  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 V1.79.1" /><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><div xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 12.4 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></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</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 and

  4.     manipulating string values.  Strings in this context include values

  5.     of the types <code class="type">character</code>, <code class="type">character varying</code>,

  6.     and <code class="type">text</code>.  Unless otherwise noted, all

  7.     of the functions listed below work on all of these types, but be

  8.     wary of potential effects of automatic space-padding when using the

  9.     <code class="type">character</code> type.  Some functions also exist

  10.     natively for the bit-string types.

  11.    </p><p>

  12.     <acronym class="acronym">SQL</acronym> defines some string functions that use

  13.     key words, rather than commas, to separate

  14.     arguments.  Details are in

  15.     <a class="xref" href="functions-string.html#FUNCTIONS-STRING-SQL" title="Table 9.9. SQL String Functions and Operators">Table 9.9</a>.

  16.     <span class="productname">PostgreSQL</span> also provides versions of these functions

  17.     that use the regular function invocation syntax

  18.     (see <a class="xref" href="functions-string.html#FUNCTIONS-STRING-OTHER" title="Table 9.10. Other String Functions">Table 9.10</a>).

  19.    </p><div class="note"><h3 class="title">Note</h3><p>

  20.      Before <span class="productname">PostgreSQL</span> 8.3, these functions would

  21.      silently accept values of several non-string data types as well, due to

  22.      the presence of implicit coercions from those data types to

  23.      <code class="type">text</code>.  Those coercions have been removed because they frequently

  24.      caused surprising behaviors.  However, the string concatenation operator

  25.      (<code class="literal">||</code>) still accepts non-string input, so long as at least one

  26.      input is of a string type, as shown 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, insert an explicit

  27.      coercion to <code class="type">text</code> if you need to duplicate the previous behavior.

  28.     </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 /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td><code class="literal"><em class="parameter"><code>string</code></em> <code class="literal">||</code>

  29.         <em class="parameter"><code>string</code></em></code></td><td> <code class="type">text</code> </td><td>

  30.         String concatenation

  31.         <a id="id-1.5.8.9.5.2.2.1.3.1" class="indexterm"></a>

  32.        </td><td><code class="literal">'Post' || 'greSQL'</code></td><td><code class="literal">PostgreSQL</code></td></tr><tr><td>

  33.         <code class="literal"><em class="parameter"><code>string</code></em> <code class="literal">||</code>

  34.         <em class="parameter"><code>non-string</code></em></code>

  35.         or

  36.         <code class="literal"><em class="parameter"><code>non-string</code></em> <code class="literal">||</code>

  37.         <em class="parameter"><code>string</code></em></code>

  38.        </td><td> <code class="type">text</code> </td><td>

  39.         String concatenation with one non-string input

  40.        </td><td><code class="literal">'Value: ' || 42</code></td><td><code class="literal">Value: 42</code></td></tr><tr><td>

  41.         <a id="id-1.5.8.9.5.2.2.3.1.1" class="indexterm"></a>

  42.         <code class="literal"><code class="function">bit_length(<em class="parameter"><code>string</code></em>)</code></code>

  43.        </td><td><code class="type">int</code></td><td>Number of bits in string</td><td><code class="literal">bit_length('jose')</code></td><td><code class="literal">32</code></td></tr><tr><td>

  44.         <a id="id-1.5.8.9.5.2.2.4.1.1" class="indexterm"></a>

  45.         <code class="literal"><code class="function">char_length(<em class="parameter"><code>string</code></em>)</code></code> or <code class="literal"><code class="function">character_length(<em class="parameter"><code>string</code></em>)</code></code>

  46.        </td><td><code class="type">int</code></td><td>

  47.         Number of characters in string

  48.         <a id="id-1.5.8.9.5.2.2.4.3.1" class="indexterm"></a>

  49.         <a id="id-1.5.8.9.5.2.2.4.3.2" class="indexterm"></a>

  50.        </td><td><code class="literal">char_length('jose')</code></td><td><code class="literal">4</code></td></tr><tr><td>

  51.         <a id="id-1.5.8.9.5.2.2.5.1.1" class="indexterm"></a>

  52.         <code class="literal"><code class="function">lower(<em class="parameter"><code>string</code></em>)</code></code>

  53.        </td><td><code class="type">text</code></td><td>Convert string to lower case</td><td><code class="literal">lower('TOM')</code></td><td><code class="literal">tom</code></td></tr><tr><td>

  54.         <a id="id-1.5.8.9.5.2.2.6.1.1" class="indexterm"></a>

  55.         <code class="literal"><code class="function">octet_length(<em class="parameter"><code>string</code></em>)</code></code>

  56.        </td><td><code class="type">int</code></td><td>Number of bytes in string</td><td><code class="literal">octet_length('jose')</code></td><td><code class="literal">4</code></td></tr><tr><td>

  57.         <a id="id-1.5.8.9.5.2.2.7.1.1" class="indexterm"></a>

  58.         <code class="literal"><code class="function">overlay(<em class="parameter"><code>string</code></em> placing <em class="parameter"><code>string</code></em> from <code class="type">int</code> [<span class="optional">for <code class="type">int</code></span>])</code></code>

  59.        </td><td><code class="type">text</code></td><td>

  60.         Replace substring

  61.        </td><td><code class="literal">overlay('Txxxxas' placing 'hom' from 2 for 4)</code></td><td><code class="literal">Thomas</code></td></tr><tr><td>

  62.         <a id="id-1.5.8.9.5.2.2.8.1.1" class="indexterm"></a>

  63.         <code class="literal"><code class="function">position(<em class="parameter"><code>substring</code></em> in <em class="parameter"><code>string</code></em>)</code></code>

  64.        </td><td><code class="type">int</code></td><td>Location of specified substring</td><td><code class="literal">position('om' in 'Thomas')</code></td><td><code class="literal">3</code></td></tr><tr><td>

  65.         <a id="id-1.5.8.9.5.2.2.9.1.1" class="indexterm"></a>

  66.         <code class="literal"><code class="function">substring(<em class="parameter"><code>string</code></em> [<span class="optional">from <code class="type">int</code></span>] [<span class="optional">for <code class="type">int</code></span>])</code></code>

  67.        </td><td><code class="type">text</code></td><td>

  68.         Extract substring

  69.        </td><td><code class="literal">substring('Thomas' from 2 for 3)</code></td><td><code class="literal">hom</code></td></tr><tr><td><code class="literal"><code class="function">substring(<em class="parameter"><code>string</code></em> from <em class="replaceable"><code>pattern</code></em>)</code></code></td><td><code class="type">text</code></td><td>

  70.         Extract substring matching POSIX regular expression. See

  71.         <a class="xref" href="functions-matching.html" title="9.7. Pattern Matching">Section 9.7</a> for more information on pattern

  72.         matching.

  73.        </td><td><code class="literal">substring('Thomas' from '...$')</code></td><td><code class="literal">mas</code></td></tr><tr><td><code class="literal"><code class="function">substring(<em class="parameter"><code>string</code></em> from <em class="replaceable"><code>pattern</code></em> for <em class="replaceable"><code>escape</code></em>)</code></code></td><td><code class="type">text</code></td><td>

  74.         Extract substring matching <acronym class="acronym">SQL</acronym> regular expression.

  75.         See <a class="xref" href="functions-matching.html" title="9.7. Pattern Matching">Section 9.7</a> for more information on

  76.         pattern matching.

  77.        </td><td><code class="literal">substring('Thomas' from '%#"o_a#"_' for '#')</code></td><td><code class="literal">oma</code></td></tr><tr><td>

  78.         <a id="id-1.5.8.9.5.2.2.12.1.1" class="indexterm"></a>

  79.         <code class="literal"><code class="function">trim([<span class="optional">leading | trailing | both</span>]

  80.         [<span class="optional"><em class="parameter"><code>characters</code></em></span>] from

  81.         <em class="parameter"><code>string</code></em>)</code></code>

  82.        </td><td><code class="type">text</code></td><td>

  83.         Remove the longest string containing only characters from

  84.         <em class="parameter"><code>characters</code></em> (a space by default) from the

  85.         start, end, or both ends (<code class="literal">both</code> is the default)

  86.         of <em class="parameter"><code>string</code></em>

  87.        </td><td><code class="literal">trim(both 'xyz' from 'yxTomxx')</code></td><td><code class="literal">Tom</code></td></tr><tr><td>

  88.         <code class="literal"><code class="function">trim([<span class="optional">leading | trailing

  89.         | both</span>] [<span class="optional">from</span>]

  90.         <em class="parameter"><code>string</code></em>

  91.         [<span class="optional">, <em class="parameter"><code>characters</code></em></span>]

  92.         )</code></code>

  93.        </td><td><code class="type">text</code></td><td>

  94.         Non-standard syntax for <code class="function">trim()</code>

  95.        </td><td><code class="literal">trim(both from 'yxTomxx', 'xyz')</code></td><td><code class="literal">Tom</code></td></tr><tr><td>

  96.         <a id="id-1.5.8.9.5.2.2.14.1.1" class="indexterm"></a>

  97.         <code class="literal"><code class="function">upper(<em class="parameter"><code>string</code></em>)</code></code>

  98.        </td><td><code class="type">text</code></td><td>Convert string to upper case</td><td><code class="literal">upper('tom')</code></td><td><code class="literal">TOM</code></td></tr></tbody></table></div></div><br class="table-break" /><p>

  99.     Additional string manipulation functions are available and are

  100.     listed in <a class="xref" href="functions-string.html#FUNCTIONS-STRING-OTHER" title="Table 9.10. Other String Functions">Table 9.10</a>.  Some of them are used internally to implement the

  101.     <acronym class="acronym">SQL</acronym>-standard string functions listed in <a class="xref" href="functions-string.html#FUNCTIONS-STRING-SQL" title="Table 9.9. SQL String Functions and Operators">Table 9.9</a>.

  102.    </p><div class="table" id="FUNCTIONS-STRING-OTHER"><p class="title"><strong>Table 9.10. Other String Functions</strong></p><div class="table-contents"><table class="table" summary="Other String Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td>

  103.         <a id="id-1.5.8.9.7.2.2.1.1.1" class="indexterm"></a>

  104.         <code class="literal"><code class="function">ascii(<em class="parameter"><code>string</code></em>)</code></code>

  105.        </td><td><code class="type">int</code></td><td>

  106.         <acronym class="acronym">ASCII</acronym> code of the first character of the

  107.         argument.  For <acronym class="acronym">UTF8</acronym> returns the Unicode code

  108.         point of the character.  For other multibyte encodings, the

  109.         argument must be an <acronym class="acronym">ASCII</acronym> character.

  110.        </td><td><code class="literal">ascii('x')</code></td><td><code class="literal">120</code></td></tr><tr><td>

  111.         <a id="id-1.5.8.9.7.2.2.2.1.1" class="indexterm"></a>

  112.         <code class="literal"><code class="function">btrim(<em class="parameter"><code>string</code></em> <code class="type">text</code>

  113.         [<span class="optional">, <em class="parameter"><code>characters</code></em> <code class="type">text</code></span>])</code></code>

  114.        </td><td><code class="type">text</code></td><td>

  115.         Remove the longest string consisting only of characters

  116.         in <em class="parameter"><code>characters</code></em> (a space by default)

  117.         from the start and end of <em class="parameter"><code>string</code></em>

  118.        </td><td><code class="literal">btrim('xyxtrimyyx', 'xyz')</code></td><td><code class="literal">trim</code></td></tr><tr><td>

  119.         <a id="id-1.5.8.9.7.2.2.3.1.1" class="indexterm"></a>

  120.         <code class="literal"><code class="function">chr(<code class="type">int</code>)</code></code>

  121.        </td><td><code class="type">text</code></td><td>

  122.         Character with the given code. For <acronym class="acronym">UTF8</acronym> the

  123.         argument is treated as a Unicode code point. For other multibyte

  124.         encodings the argument must designate an

  125.         <acronym class="acronym">ASCII</acronym> character.  The NULL (0) character is not

  126.         allowed because text data types cannot store such bytes.

  127.        </td><td><code class="literal">chr(65)</code></td><td><code class="literal">A</code></td></tr><tr><td>

  128.         <a id="id-1.5.8.9.7.2.2.4.1.1" class="indexterm"></a>

  129.         <code class="literal"><code class="function">concat(<em class="parameter"><code>str</code></em> <code class="type">"any"</code>

  130.          [, <em class="parameter"><code>str</code></em> <code class="type">"any"</code> [, ...] ])</code></code>

  131.        </td><td><code class="type">text</code></td><td>

  132.         Concatenate the text representations of all the arguments.

  133.         NULL arguments are ignored.

  134.        </td><td><code class="literal">concat('abcde', 2, NULL, 22)</code></td><td><code class="literal">abcde222</code></td></tr><tr><td>

  135.         <a id="id-1.5.8.9.7.2.2.5.1.1" class="indexterm"></a>

  136.         <code class="literal"><code class="function">concat_ws(<em class="parameter"><code>sep</code></em> <code class="type">text</code>,

  137.         <em class="parameter"><code>str</code></em> <code class="type">"any"</code>

  138.         [, <em class="parameter"><code>str</code></em> <code class="type">"any"</code> [, ...] ])</code></code>

  139.        </td><td><code class="type">text</code></td><td>

  140.         Concatenate all but the first argument with separators. The first

  141.         argument is used as the separator string. NULL arguments are ignored.

  142.        </td><td><code class="literal">concat_ws(',', 'abcde', 2, NULL, 22)</code></td><td><code class="literal">abcde,2,22</code></td></tr><tr><td>

  143.         <a id="id-1.5.8.9.7.2.2.6.1.1" class="indexterm"></a>

  144.         <code class="literal"><code class="function">convert(<em class="parameter"><code>string</code></em> <code class="type">bytea</code>,

  145.         <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code>,

  146.         <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code>)</code></code>

  147.        </td><td><code class="type">bytea</code></td><td>

  148.         Convert string to <em class="parameter"><code>dest_encoding</code></em>.  The

  149.         original encoding is specified by

  150.         <em class="parameter"><code>src_encoding</code></em>. The

  151.         <em class="parameter"><code>string</code></em> must be valid in this encoding.

  152.         Conversions can be defined by <code class="command">CREATE CONVERSION</code>.

  153.         Also there are some predefined conversions. See <a class="xref" href="functions-string.html#CONVERSION-NAMES" title="Table 9.11. Built-in Conversions">Table 9.11</a> for available conversions.

  154.        </td><td><code class="literal">convert('text_in_utf8', 'UTF8', 'LATIN1')</code></td><td><code class="literal">text_in_utf8</code> represented in Latin-1

  155.        encoding (ISO 8859-1)</td></tr><tr><td>

  156.         <a id="id-1.5.8.9.7.2.2.7.1.1" class="indexterm"></a>

  157.         <code class="literal"><code class="function">convert_from(<em class="parameter"><code>string</code></em> <code class="type">bytea</code>,

  158.         <em class="parameter"><code>src_encoding</code></em> <code class="type">name</code>)</code></code>

  159.        </td><td><code class="type">text</code></td><td>

  160.         Convert string to the database encoding.  The original encoding

  161.         is specified by <em class="parameter"><code>src_encoding</code></em>. The

  162.         <em class="parameter"><code>string</code></em> must be valid in this encoding.

  163.        </td><td><code class="literal">convert_from('text_in_utf8', 'UTF8')</code></td><td><code class="literal">text_in_utf8</code> represented in the current database encoding</td></tr><tr><td>

  164.         <a id="id-1.5.8.9.7.2.2.8.1.1" class="indexterm"></a>

  165.         <code class="literal"><code class="function">convert_to(<em class="parameter"><code>string</code></em> <code class="type">text</code>,

  166.         <em class="parameter"><code>dest_encoding</code></em> <code class="type">name</code>)</code></code>

  167.        </td><td><code class="type">bytea</code></td><td>

  168.         Convert string to <em class="parameter"><code>dest_encoding</code></em>.

  169.        </td><td><code class="literal">convert_to('some text', 'UTF8')</code></td><td><code class="literal">some text</code> represented in the UTF8 encoding</td></tr><tr><td>

  170.         <a id="id-1.5.8.9.7.2.2.9.1.1" class="indexterm"></a>

  171.         <code class="literal"><code class="function">decode(<em class="parameter"><code>string</code></em> <code class="type">text</code>,

  172.         <em class="parameter"><code>format</code></em> <code class="type">text</code>)</code></code>

  173.        </td><td><code class="type">bytea</code></td><td>

  174.         Decode binary data from textual representation in <em class="parameter"><code>string</code></em>.

  175.         Options for <em class="parameter"><code>format</code></em> are same as in <code class="function">encode</code>.

  176.        </td><td><code class="literal">decode('MTIzAAE=', 'base64')</code></td><td><code class="literal">\x3132330001</code></td></tr><tr><td>

  177.         <a id="id-1.5.8.9.7.2.2.10.1.1" class="indexterm"></a>

  178.         <code class="literal"><code class="function">encode(<em class="parameter"><code>data</code></em> <code class="type">bytea</code>,

  179.         <em class="parameter"><code>format</code></em> <code class="type">text</code>)</code></code>

  180.        </td><td><code class="type">text</code></td><td>

  181.         Encode binary data into a textual representation.  Supported

  182.         formats are: <code class="literal">base64</code>, <code class="literal">hex</code>, <code class="literal">escape</code>.

  183.         <code class="literal">escape</code> converts zero bytes and high-bit-set bytes to

  184.         octal sequences (<code class="literal">\</code><em class="replaceable"><code>nnn</code></em>) and

  185.         doubles backslashes.

  186.        </td><td><code class="literal">encode('123\000\001', 'base64')</code></td><td><code class="literal">MTIzAAE=</code></td></tr><tr><td id="FORMAT">

  187.         <a id="id-1.5.8.9.7.2.2.11.1.1" class="indexterm"></a>

  188.         <code class="literal"><code class="function">format</code>(<em class="parameter"><code>formatstr</code></em> <code class="type">text</code>

  189.         [, <em class="parameter"><code>formatarg</code></em> <code class="type">"any"</code> [, ...] ])</code>

  190.        </td><td><code class="type">text</code></td><td>

  191.          Format arguments according to a format string.

  192.          This function is similar to the C function <code class="function">sprintf</code>.

  193.          See <a class="xref" href="functions-string.html#FUNCTIONS-STRING-FORMAT" title="9.4.1. format">Section 9.4.1</a>.

  194.        </td><td><code class="literal">format('Hello %s, %1$s', 'World')</code></td><td><code class="literal">Hello World, World</code></td></tr><tr><td>

  195.         <a id="id-1.5.8.9.7.2.2.12.1.1" class="indexterm"></a>

  196.         <code class="literal"><code class="function">initcap(<em class="parameter"><code>string</code></em>)</code></code>

  197.        </td><td><code class="type">text</code></td><td>

  198.         Convert the first letter of each word to upper case and the

  199.         rest to lower case. Words are sequences of alphanumeric

  200.         characters separated by non-alphanumeric characters.

  201.        </td><td><code class="literal">initcap('hi THOMAS')</code></td><td><code class="literal">Hi Thomas</code></td></tr><tr><td>

  202.         <a id="id-1.5.8.9.7.2.2.13.1.1" class="indexterm"></a>

  203.         <code class="literal"><code class="function">left(<em class="parameter"><code>str</code></em> <code class="type">text</code>,

  204.         <em class="parameter"><code>n</code></em> <code class="type">int</code>)</code></code>

  205.        </td><td><code class="type">text</code></td><td>

  206.         Return first <em class="replaceable"><code>n</code></em> characters in the string. When <em class="replaceable"><code>n</code></em>

  207.         is negative, return all but last |<em class="replaceable"><code>n</code></em>| characters.

  208.         </td><td><code class="literal">left('abcde', 2)</code></td><td><code class="literal">ab</code></td></tr><tr><td>

  209.         <a id="id-1.5.8.9.7.2.2.14.1.1" class="indexterm"></a>

  210.         <code class="literal"><code class="function">length(<em class="parameter"><code>string</code></em>)</code></code>

  211.        </td><td><code class="type">int</code></td><td>

  212.         Number of characters in <em class="parameter"><code>string</code></em>

  213.        </td><td><code class="literal">length('jose')</code></td><td><code class="literal">4</code></td></tr><tr><td><code class="literal"><code class="function">length(<em class="parameter"><code>string</code></em> <code class="type">bytea</code>,

  214.         <em class="parameter"><code>encoding</code></em> <code class="type">name</code> )</code></code></td><td><code class="type">int</code></td><td>

  215.         Number of characters in <em class="parameter"><code>string</code></em> in the given

  216.         <em class="parameter"><code>encoding</code></em>. The <em class="parameter"><code>string</code></em>

  217.         must be valid in this encoding.

  218.        </td><td><code class="literal">length('jose', 'UTF8')</code></td><td><code class="literal">4</code></td></tr><tr><td>

  219.         <a id="id-1.5.8.9.7.2.2.16.1.1" class="indexterm"></a>

  220.         <code class="literal"><code class="function">lpad(<em class="parameter"><code>string</code></em> <code class="type">text</code>,

  221.         <em class="parameter"><code>length</code></em> <code class="type">int</code>

  222.         [<span class="optional">, <em class="parameter"><code>fill</code></em> <code class="type">text</code></span>])</code></code>

  223.        </td><td><code class="type">text</code></td><td>

  224.         Fill up the <em class="parameter"><code>string</code></em> to length

  225.         <em class="parameter"><code>length</code></em> by prepending the characters

  226.         <em class="parameter"><code>fill</code></em> (a space by default).  If the

  227.         <em class="parameter"><code>string</code></em> is already longer than

  228.         <em class="parameter"><code>length</code></em> then it is truncated (on the

  229.         right).

  230.        </td><td><code class="literal">lpad('hi', 5, 'xy')</code></td><td><code class="literal">xyxhi</code></td></tr><tr><td>

  231.         <a id="id-1.5.8.9.7.2.2.17.1.1" class="indexterm"></a>

  232.         <code class="literal"><code class="function">ltrim(<em class="parameter"><code>string</code></em> <code class="type">text</code>

  233.         [<span class="optional">, <em class="parameter"><code>characters</code></em> <code class="type">text</code></span>])</code></code>

  234.        </td><td><code class="type">text</code></td><td>

  235.         Remove the longest string containing only characters from

  236.         <em class="parameter"><code>characters</code></em> (a space by default) from the start of

  237.         <em class="parameter"><code>string</code></em>

  238.        </td><td><code class="literal">ltrim('zzzytest', 'xyz')</code></td><td><code class="literal">test</code></td></tr><tr><td>

  239.         <a id="id-1.5.8.9.7.2.2.18.1.1" class="indexterm"></a>

  240.         <code class="literal"><code class="function">md5(<em class="parameter"><code>string</code></em>)</code></code>

  241.        </td><td><code class="type">text</code></td><td>

  242.         Calculates the MD5 hash of <em class="parameter"><code>string</code></em>,

  243.         returning the result in hexadecimal

  244.        </td><td><code class="literal">md5('abc')</code></td><td><code class="literal">900150983cd24fb0 d6963f7d28e17f72</code></td></tr><tr><td>

  245.         <a id="id-1.5.8.9.7.2.2.19.1.1" class="indexterm"></a>

  246.         <code class="literal"><code class="function">parse_ident(<em class="parameter"><code>qualified_identifier</code></em> <code class="type">text</code>

  247.            [, <em class="parameter"><code>strictmode</code></em> <code class="type">boolean</code> DEFAULT true ] )</code></code>

  248.        </td><td><code class="type">text[]</code></td><td>

  249.         Split <em class="parameter"><code>qualified_identifier</code></em> into an array of

  250.         identifiers, removing any quoting of individual identifiers.  By

  251.         default, extra characters after the last identifier are considered an

  252.         error; but if the second parameter is <code class="literal">false</code>, then such

  253.         extra characters are ignored. (This behavior is useful for parsing

  254.         names for objects like functions.) Note that this function does not

  255.         truncate over-length identifiers. If you want truncation you can cast

  256.         the result to <code class="type">name[]</code>.

  257.        </td><td><code class="literal">parse_ident('"SomeSchema".someTable')</code></td><td><code class="literal">{SomeSchema,sometable}</code></td></tr><tr><td>

  258.         <a id="id-1.5.8.9.7.2.2.20.1.1" class="indexterm"></a>

  259.         <code class="literal"><code class="function">pg_client_encoding()</code></code>

  260.        </td><td><code class="type">name</code></td><td>

  261.         Current client encoding name

  262.        </td><td><code class="literal">pg_client_encoding()</code></td><td><code class="literal">SQL_ASCII</code></td></tr><tr><td>

  263.         <a id="id-1.5.8.9.7.2.2.21.1.1" class="indexterm"></a>

  264.         <code class="literal"><code class="function">quote_ident(<em class="parameter"><code>string</code></em> <code class="type">text</code>)</code></code>

  265.        </td><td><code class="type">text</code></td><td>

  266.         Return the given string suitably quoted to be used as an identifier

  267.         in an <acronym class="acronym">SQL</acronym> statement string.

  268.         Quotes are added only if necessary (i.e., if the string contains

  269.         non-identifier characters or would be case-folded).

  270.         Embedded quotes are properly doubled.

  271.         See also <a class="xref" href="plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" title="Example 42.1. Quoting Values in Dynamic Queries">Example 42.1</a>.

  272.        </td><td><code class="literal">quote_ident('Foo bar')</code></td><td><code class="literal">"Foo bar"</code></td></tr><tr><td>

  273.         <a id="id-1.5.8.9.7.2.2.22.1.1" class="indexterm"></a>

  274.         <code class="literal"><code class="function">quote_literal(<em class="parameter"><code>string</code></em> <code class="type">text</code>)</code></code>

  275.        </td><td><code class="type">text</code></td><td>

  276.         Return the given string suitably quoted to be used as a string literal

  277.         in an <acronym class="acronym">SQL</acronym> statement string.

  278.         Embedded single-quotes and backslashes are properly doubled.

  279.         Note that <code class="function">quote_literal</code> returns null on null

  280.         input; if the argument might be null,

  281.         <code class="function">quote_nullable</code> is often more suitable.

  282.         See also <a class="xref" href="plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" title="Example 42.1. Quoting Values in Dynamic Queries">Example 42.1</a>.

  283.        </td><td><code class="literal">quote_literal(E'O\'Reilly')</code></td><td><code class="literal">'O''Reilly'</code></td></tr><tr><td><code class="literal"><code class="function">quote_literal(<em class="parameter"><code>value</code></em> <code class="type">anyelement</code>)</code></code></td><td><code class="type">text</code></td><td>

  284.         Coerce the given value to text and then quote it as a literal.

  285.         Embedded single-quotes and backslashes are properly doubled.

  286.        </td><td><code class="literal">quote_literal(42.5)</code></td><td><code class="literal">'42.5'</code></td></tr><tr><td>

  287.         <a id="id-1.5.8.9.7.2.2.24.1.1" class="indexterm"></a>

  288.         <code class="literal"><code class="function">quote_nullable(<em class="parameter"><code>string</code></em> <code class="type">text</code>)</code></code>

  289.        </td><td><code class="type">text</code></td><td>

  290.         Return the given string suitably quoted to be used as a string literal

  291.         in an <acronym class="acronym">SQL</acronym> statement string; or, if the argument

  292.         is null, return <code class="literal">NULL</code>.

  293.         Embedded single-quotes and backslashes are properly doubled.

  294.         See also <a class="xref" href="plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" title="Example 42.1. Quoting Values in Dynamic Queries">Example 42.1</a>.

  295.        </td><td><code class="literal">quote_nullable(NULL)</code></td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal"><code class="function">quote_nullable(<em class="parameter"><code>value</code></em> <code class="type">anyelement</code>)</code></code></td><td><code class="type">text</code></td><td>

  296.         Coerce the given value to text and then quote it as a literal;

  297.         or, if the argument is null, return <code class="literal">NULL</code>.

  298.         Embedded single-quotes and backslashes are properly doubled.

  299.        </td><td><code class="literal">quote_nullable(42.5)</code></td><td><code class="literal">'42.5'</code></td></tr><tr><td>

  300.         <a id="id-1.5.8.9.7.2.2.26.1.1" class="indexterm"></a>

  301.         <code class="literal"><code class="function">regexp_match(<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>])</code></code>

  302.        </td><td><code class="type">text[]</code></td><td>

  303.         Return captured substring(s) resulting from the first match of a POSIX

  304.         regular expression to the <em class="parameter"><code>string</code></em>. See

  305.         <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a> for more information.

  306.        </td><td><code class="literal">regexp_match('foobarbequebaz', '(bar)(beque)')</code></td><td><code class="literal">{bar,beque}</code></td></tr><tr><td>

  307.         <a id="id-1.5.8.9.7.2.2.27.1.1" class="indexterm"></a>

  308.         <code class="literal"><code class="function">regexp_matches(<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>])</code></code>

  309.        </td><td><code class="type">setof text[]</code></td><td>

  310.         Return captured substring(s) resulting from matching a POSIX regular

  311.         expression to the <em class="parameter"><code>string</code></em>. See

  312.         <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a> for more information.

  313.        </td><td><code class="literal">regexp_matches('foobarbequebaz', 'ba.', 'g')</code></td><td><code class="literal">{bar}</code><p><code class="literal">{baz}</code></p> (2 rows)</td></tr><tr><td>

  314.         <a id="id-1.5.8.9.7.2.2.28.1.1" class="indexterm"></a>

  315.         <code class="literal"><code class="function">regexp_replace(<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> [, <em class="parameter"><code>flags</code></em> <code class="type">text</code>])</code></code>

  316.        </td><td><code class="type">text</code></td><td>

  317.         Replace substring(s) matching a POSIX regular expression. See

  318.         <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a> for more information.

  319.        </td><td><code class="literal">regexp_replace('Thomas', '.[mN]a.', 'M')</code></td><td><code class="literal">ThM</code></td></tr><tr><td>

  320.         <a id="id-1.5.8.9.7.2.2.29.1.1" class="indexterm"></a>

  321.         <code class="literal"><code class="function">regexp_split_to_array(<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> ])</code></code>

  322.        </td><td><code class="type">text[]</code></td><td>

  323.         Split <em class="parameter"><code>string</code></em> using a POSIX regular expression as

  324.         the delimiter.  See <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a> for more

  325.         information.

  326.        </td><td><code class="literal">regexp_split_to_array('hello world', '\s+')</code></td><td><code class="literal">{hello,world}</code></td></tr><tr><td>

  327.         <a id="id-1.5.8.9.7.2.2.30.1.1" class="indexterm"></a>

  328.         <code class="literal"><code class="function">regexp_split_to_table(<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>])</code></code>

  329.        </td><td><code class="type">setof text</code></td><td>

  330.         Split <em class="parameter"><code>string</code></em> using a POSIX regular expression as

  331.         the delimiter.  See <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a> for more

  332.         information.

  333.        </td><td><code class="literal">regexp_split_to_table('hello world', '\s+')</code></td><td><code class="literal">hello</code><p><code class="literal">world</code></p> (2 rows)</td></tr><tr><td>

  334.         <a id="id-1.5.8.9.7.2.2.31.1.1" class="indexterm"></a>

  335.         <code class="literal"><code class="function">repeat(<em class="parameter"><code>string</code></em> <code class="type">text</code>, <em class="parameter"><code>number</code></em> <code class="type">int</code>)</code></code>

  336.        </td><td><code class="type">text</code></td><td>Repeat <em class="parameter"><code>string</code></em> the specified

  337.        <em class="parameter"><code>number</code></em> of times</td><td><code class="literal">repeat('Pg', 4)</code></td><td><code class="literal">PgPgPgPg</code></td></tr><tr><td>

  338.         <a id="id-1.5.8.9.7.2.2.32.1.1" class="indexterm"></a>

  339.         <code class="literal"><code class="function">replace(<em class="parameter"><code>string</code></em> <code class="type">text</code>,

  340.         <em class="parameter"><code>from</code></em> <code class="type">text</code>,

  341.         <em class="parameter"><code>to</code></em> <code class="type">text</code>)</code></code>

  342.        </td><td><code class="type">text</code></td><td>Replace all occurrences in <em class="parameter"><code>string</code></em> of substring

  343.         <em class="parameter"><code>from</code></em> with substring <em class="parameter"><code>to</code></em>

  344.        </td><td><code class="literal">replace('abcdefabcdef', 'cd', 'XX')</code></td><td><code class="literal">abXXefabXXef</code></td></tr><tr><td>

  345.         <a id="id-1.5.8.9.7.2.2.33.1.1" class="indexterm"></a>

  346.         <code class="literal"><code class="function">reverse(<em class="parameter"><code>str</code></em>)</code></code>

  347.        </td><td><code class="type">text</code></td><td>

  348.         Return reversed string.

  349.        </td><td><code class="literal">reverse('abcde')</code></td><td><code class="literal">edcba</code></td></tr><tr><td>

  350.         <a id="id-1.5.8.9.7.2.2.34.1.1" class="indexterm"></a>

  351.         <code class="literal"><code class="function">right(<em class="parameter"><code>str</code></em> <code class="type">text</code>,

  352.          <em class="parameter"><code>n</code></em> <code class="type">int</code>)</code></code>

  353.        </td><td><code class="type">text</code></td><td>

  354.         Return last <em class="replaceable"><code>n</code></em> characters in the string. When <em class="replaceable"><code>n</code></em>

  355.         is negative, return all but first |<em class="replaceable"><code>n</code></em>| characters.

  356.        </td><td><code class="literal">right('abcde', 2)</code></td><td><code class="literal">de</code></td></tr><tr><td>

  357.         <a id="id-1.5.8.9.7.2.2.35.1.1" class="indexterm"></a>

  358.         <code class="literal"><code class="function">rpad(<em class="parameter"><code>string</code></em> <code class="type">text</code>,

  359.         <em class="parameter"><code>length</code></em> <code class="type">int</code>

  360.         [<span class="optional">, <em class="parameter"><code>fill</code></em> <code class="type">text</code></span>])</code></code>

  361.        </td><td><code class="type">text</code></td><td>

  362.         Fill up the <em class="parameter"><code>string</code></em> to length

  363.         <em class="parameter"><code>length</code></em> by appending the characters

  364.         <em class="parameter"><code>fill</code></em> (a space by default).  If the

  365.         <em class="parameter"><code>string</code></em> is already longer than

  366.         <em class="parameter"><code>length</code></em> then it is truncated.

  367.        </td><td><code class="literal">rpad('hi', 5, 'xy')</code></td><td><code class="literal">hixyx</code></td></tr><tr><td>

  368.         <a id="id-1.5.8.9.7.2.2.36.1.1" class="indexterm"></a>

  369.         <code class="literal"><code class="function">rtrim(<em class="parameter"><code>string</code></em> <code class="type">text</code>

  370.          [<span class="optional">, <em class="parameter"><code>characters</code></em> <code class="type">text</code></span>])</code></code>

  371.        </td><td><code class="type">text</code></td><td>

  372.         Remove the longest string containing only characters from

  373.         <em class="parameter"><code>characters</code></em> (a space by default) from the end of

  374.         <em class="parameter"><code>string</code></em>

  375.        </td><td><code class="literal">rtrim('testxxzx', 'xyz')</code></td><td><code class="literal">test</code></td></tr><tr><td>

  376.         <a id="id-1.5.8.9.7.2.2.37.1.1" class="indexterm"></a>

  377.         <code class="literal"><code class="function">split_part(<em class="parameter"><code>string</code></em> <code class="type">text</code>,

  378.         <em class="parameter"><code>delimiter</code></em> <code class="type">text</code>,

  379.         <em class="parameter"><code>field</code></em> <code class="type">int</code>)</code></code>

  380.        </td><td><code class="type">text</code></td><td>Split <em class="parameter"><code>string</code></em> on <em class="parameter"><code>delimiter</code></em>

  381.         and return the given field (counting from one)

  382.        </td><td><code class="literal">split_part('abc~@~def~@~ghi', '~@~', 2)</code></td><td><code class="literal">def</code></td></tr><tr><td>

  383.         <a id="id-1.5.8.9.7.2.2.38.1.1" class="indexterm"></a>

  384.         <code class="literal"><code class="function">strpos(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>substring</code></em>)</code></code>

  385.        </td><td><code class="type">int</code></td><td>

  386.         Location of specified substring (same as

  387.         <code class="literal">position(<em class="parameter"><code>substring</code></em> in

  388.          <em class="parameter"><code>string</code></em>)</code>, but note the reversed

  389.         argument order)

  390.        </td><td><code class="literal">strpos('high', 'ig')</code></td><td><code class="literal">2</code></td></tr><tr><td>

  391.         <a id="id-1.5.8.9.7.2.2.39.1.1" class="indexterm"></a>

  392.         <code class="literal"><code class="function">substr(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>from</code></em> [<span class="optional">, <em class="parameter"><code>count</code></em></span>])</code></code>

  393.        </td><td><code class="type">text</code></td><td>

  394.         Extract substring (same as

  395.         <code class="literal">substring(<em class="parameter"><code>string</code></em> from <em class="parameter"><code>from</code></em> for <em class="parameter"><code>count</code></em>)</code>)

  396.        </td><td><code class="literal">substr('alphabet', 3, 2)</code></td><td><code class="literal">ph</code></td></tr><tr><td>

  397.         <a id="id-1.5.8.9.7.2.2.40.1.1" class="indexterm"></a>

  398.         <code class="literal"><code class="function">starts_with(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>prefix</code></em>)</code></code>

  399.        </td><td><code class="type">bool</code></td><td>

  400.         Returns true if <em class="parameter"><code>string</code></em> starts with <em class="parameter"><code>prefix</code></em>.

  401.        </td><td><code class="literal">starts_with('alphabet', 'alph')</code></td><td><code class="literal">t</code></td></tr><tr><td>

  402.         <a id="id-1.5.8.9.7.2.2.41.1.1" class="indexterm"></a>

  403.         <code class="literal"><code class="function">to_ascii(<em class="parameter"><code>string</code></em> <code class="type">text</code>

  404.         [<span class="optional">, <em class="parameter"><code>encoding</code></em> <code class="type">text</code></span>])</code></code>

  405.        </td><td><code class="type">text</code></td><td>

  406.        Convert <em class="parameter"><code>string</code></em> to <acronym class="acronym">ASCII</acronym> from another encoding

  407.        (only supports conversion from  <code class="literal">LATIN1</code>, <code class="literal">LATIN2</code>, <code class="literal">LATIN9</code>,

  408.        and <code class="literal">WIN1250</code> encodings)

  409.        </td><td><code class="literal">to_ascii('Karel')</code></td><td><code class="literal">Karel</code></td></tr><tr><td>

  410.         <a id="id-1.5.8.9.7.2.2.42.1.1" class="indexterm"></a>

  411.         <code class="literal"><code class="function">to_hex(<em class="parameter"><code>number</code></em> <code class="type">int</code>

  412.         or <code class="type">bigint</code>)</code></code>

  413.        </td><td><code class="type">text</code></td><td>Convert <em class="parameter"><code>number</code></em> to its equivalent hexadecimal

  414.         representation

  415.        </td><td><code class="literal">to_hex(2147483647)</code></td><td><code class="literal">7fffffff</code></td></tr><tr><td>

  416.         <a id="id-1.5.8.9.7.2.2.43.1.1" class="indexterm"></a>

  417.         <code class="literal"><code class="function">translate(<em class="parameter"><code>string</code></em> <code class="type">text</code>,

  418.         <em class="parameter"><code>from</code></em> <code class="type">text</code>,

  419.         <em class="parameter"><code>to</code></em> <code class="type">text</code>)</code></code>

  420.        </td><td><code class="type">text</code></td><td>

  421.         Any character in <em class="parameter"><code>string</code></em> that matches a

  422.         character in the <em class="parameter"><code>from</code></em> set is replaced by

  423.         the corresponding character in the <em class="parameter"><code>to</code></em>

  424.         set. If <em class="parameter"><code>from</code></em> is longer than

  425.         <em class="parameter"><code>to</code></em>, occurrences of the extra characters in

  426.         <em class="parameter"><code>from</code></em> are removed.

  427.        </td><td><code class="literal">translate('12345', '143', 'ax')</code></td><td><code class="literal">a2x5</code></td></tr></tbody></table></div></div><br class="table-break" /><p>

  428.     The <code class="function">concat</code>, <code class="function">concat_ws</code> and

  429.     <code class="function">format</code> functions are variadic, so it is possible to

  430.     pass the values to be concatenated or formatted as an array marked with

  431.     the <code class="literal">VARIADIC</code> keyword (see <a class="xref" href="xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS" title="37.5.5. SQL Functions with Variable Numbers of Arguments">Section 37.5.5</a>).  The array's elements are

  432.     treated as if they were separate ordinary arguments to the function.

  433.     If the variadic array argument is NULL, <code class="function">concat</code>

  434.     and <code class="function">concat_ws</code> return NULL, but

  435.     <code class="function">format</code> treats a NULL as a zero-element array.

  436.    </p><p>

  437.    See also the aggregate function <code class="function">string_agg</code> in

  438.    <a class="xref" href="functions-aggregate.html" title="9.20. Aggregate Functions">Section 9.20</a>.

  439.    </p><div class="table" id="CONVERSION-NAMES"><p class="title"><strong>Table 9.11. Built-in Conversions</strong></p><div class="table-contents"><table class="table" summary="Built-in Conversions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Conversion Name

  440.         <a href="#ftn.id-1.5.8.9.10.2.1.1.1.1" class="footnote"><sup class="footnote" id="id-1.5.8.9.10.2.1.1.1.1">[a]</sup></a>

  441.        </th><th>Source Encoding</th><th>Destination Encoding</th></tr></thead><tbody><tr><td><code class="literal">ascii_to_mic</code></td><td><code class="literal">SQL_ASCII</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">ascii_to_utf8</code></td><td><code class="literal">SQL_ASCII</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">big5_to_euc_tw</code></td><td><code class="literal">BIG5</code></td><td><code class="literal">EUC_TW</code></td></tr><tr><td><code class="literal">big5_to_mic</code></td><td><code class="literal">BIG5</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">big5_to_utf8</code></td><td><code class="literal">BIG5</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">euc_cn_to_mic</code></td><td><code class="literal">EUC_CN</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">euc_cn_to_utf8</code></td><td><code class="literal">EUC_CN</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">euc_jp_to_mic</code></td><td><code class="literal">EUC_JP</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">euc_jp_to_sjis</code></td><td><code class="literal">EUC_JP</code></td><td><code class="literal">SJIS</code></td></tr><tr><td><code class="literal">euc_jp_to_utf8</code></td><td><code class="literal">EUC_JP</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">euc_kr_to_mic</code></td><td><code class="literal">EUC_KR</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">euc_kr_to_utf8</code></td><td><code class="literal">EUC_KR</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">euc_tw_to_big5</code></td><td><code class="literal">EUC_TW</code></td><td><code class="literal">BIG5</code></td></tr><tr><td><code class="literal">euc_tw_to_mic</code></td><td><code class="literal">EUC_TW</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">euc_tw_to_utf8</code></td><td><code class="literal">EUC_TW</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">gb18030_to_utf8</code></td><td><code class="literal">GB18030</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">gbk_to_utf8</code></td><td><code class="literal">GBK</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_10_to_utf8</code></td><td><code class="literal">LATIN6</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_13_to_utf8</code></td><td><code class="literal">LATIN7</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_14_to_utf8</code></td><td><code class="literal">LATIN8</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_15_to_utf8</code></td><td><code class="literal">LATIN9</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_16_to_utf8</code></td><td><code class="literal">LATIN10</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_1_to_mic</code></td><td><code class="literal">LATIN1</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">iso_8859_1_to_utf8</code></td><td><code class="literal">LATIN1</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_2_to_mic</code></td><td><code class="literal">LATIN2</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">iso_8859_2_to_utf8</code></td><td><code class="literal">LATIN2</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_2_to_windows_1250</code></td><td><code class="literal">LATIN2</code></td><td><code class="literal">WIN1250</code></td></tr><tr><td><code class="literal">iso_8859_3_to_mic</code></td><td><code class="literal">LATIN3</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">iso_8859_3_to_utf8</code></td><td><code class="literal">LATIN3</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_4_to_mic</code></td><td><code class="literal">LATIN4</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">iso_8859_4_to_utf8</code></td><td><code class="literal">LATIN4</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_5_to_koi8_r</code></td><td><code class="literal">ISO_8859_5</code></td><td><code class="literal">KOI8R</code></td></tr><tr><td><code class="literal">iso_8859_5_to_mic</code></td><td><code class="literal">ISO_8859_5</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">iso_8859_5_to_utf8</code></td><td><code class="literal">ISO_8859_5</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_5_to_windows_1251</code></td><td><code class="literal">ISO_8859_5</code></td><td><code class="literal">WIN1251</code></td></tr><tr><td><code class="literal">iso_8859_5_to_windows_866</code></td><td><code class="literal">ISO_8859_5</code></td><td><code class="literal">WIN866</code></td></tr><tr><td><code class="literal">iso_8859_6_to_utf8</code></td><td><code class="literal">ISO_8859_6</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_7_to_utf8</code></td><td><code class="literal">ISO_8859_7</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_8_to_utf8</code></td><td><code class="literal">ISO_8859_8</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">iso_8859_9_to_utf8</code></td><td><code class="literal">LATIN5</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">johab_to_utf8</code></td><td><code class="literal">JOHAB</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">koi8_r_to_iso_8859_5</code></td><td><code class="literal">KOI8R</code></td><td><code class="literal">ISO_8859_5</code></td></tr><tr><td><code class="literal">koi8_r_to_mic</code></td><td><code class="literal">KOI8R</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">koi8_r_to_utf8</code></td><td><code class="literal">KOI8R</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">koi8_r_to_windows_1251</code></td><td><code class="literal">KOI8R</code></td><td><code class="literal">WIN1251</code></td></tr><tr><td><code class="literal">koi8_r_to_windows_866</code></td><td><code class="literal">KOI8R</code></td><td><code class="literal">WIN866</code></td></tr><tr><td><code class="literal">koi8_u_to_utf8</code></td><td><code class="literal">KOI8U</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">mic_to_ascii</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">SQL_ASCII</code></td></tr><tr><td><code class="literal">mic_to_big5</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">BIG5</code></td></tr><tr><td><code class="literal">mic_to_euc_cn</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">EUC_CN</code></td></tr><tr><td><code class="literal">mic_to_euc_jp</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">EUC_JP</code></td></tr><tr><td><code class="literal">mic_to_euc_kr</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">EUC_KR</code></td></tr><tr><td><code class="literal">mic_to_euc_tw</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">EUC_TW</code></td></tr><tr><td><code class="literal">mic_to_iso_8859_1</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">LATIN1</code></td></tr><tr><td><code class="literal">mic_to_iso_8859_2</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">LATIN2</code></td></tr><tr><td><code class="literal">mic_to_iso_8859_3</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">LATIN3</code></td></tr><tr><td><code class="literal">mic_to_iso_8859_4</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">LATIN4</code></td></tr><tr><td><code class="literal">mic_to_iso_8859_5</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">ISO_8859_5</code></td></tr><tr><td><code class="literal">mic_to_koi8_r</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">KOI8R</code></td></tr><tr><td><code class="literal">mic_to_sjis</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">SJIS</code></td></tr><tr><td><code class="literal">mic_to_windows_1250</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">WIN1250</code></td></tr><tr><td><code class="literal">mic_to_windows_1251</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">WIN1251</code></td></tr><tr><td><code class="literal">mic_to_windows_866</code></td><td><code class="literal">MULE_INTERNAL</code></td><td><code class="literal">WIN866</code></td></tr><tr><td><code class="literal">sjis_to_euc_jp</code></td><td><code class="literal">SJIS</code></td><td><code class="literal">EUC_JP</code></td></tr><tr><td><code class="literal">sjis_to_mic</code></td><td><code class="literal">SJIS</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">sjis_to_utf8</code></td><td><code class="literal">SJIS</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">windows_1258_to_utf8</code></td><td><code class="literal">WIN1258</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">uhc_to_utf8</code></td><td><code class="literal">UHC</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">utf8_to_ascii</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">SQL_ASCII</code></td></tr><tr><td><code class="literal">utf8_to_big5</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">BIG5</code></td></tr><tr><td><code class="literal">utf8_to_euc_cn</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">EUC_CN</code></td></tr><tr><td><code class="literal">utf8_to_euc_jp</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">EUC_JP</code></td></tr><tr><td><code class="literal">utf8_to_euc_kr</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">EUC_KR</code></td></tr><tr><td><code class="literal">utf8_to_euc_tw</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">EUC_TW</code></td></tr><tr><td><code class="literal">utf8_to_gb18030</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">GB18030</code></td></tr><tr><td><code class="literal">utf8_to_gbk</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">GBK</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_1</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN1</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_10</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN6</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_13</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN7</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_14</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN8</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_15</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN9</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_16</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN10</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_2</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN2</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_3</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN3</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_4</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN4</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_5</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">ISO_8859_5</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_6</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">ISO_8859_6</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_7</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">ISO_8859_7</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_8</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">ISO_8859_8</code></td></tr><tr><td><code class="literal">utf8_to_iso_8859_9</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">LATIN5</code></td></tr><tr><td><code class="literal">utf8_to_johab</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">JOHAB</code></td></tr><tr><td><code class="literal">utf8_to_koi8_r</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">KOI8R</code></td></tr><tr><td><code class="literal">utf8_to_koi8_u</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">KOI8U</code></td></tr><tr><td><code class="literal">utf8_to_sjis</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">SJIS</code></td></tr><tr><td><code class="literal">utf8_to_windows_1258</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1258</code></td></tr><tr><td><code class="literal">utf8_to_uhc</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">UHC</code></td></tr><tr><td><code class="literal">utf8_to_windows_1250</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1250</code></td></tr><tr><td><code class="literal">utf8_to_windows_1251</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1251</code></td></tr><tr><td><code class="literal">utf8_to_windows_1252</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1252</code></td></tr><tr><td><code class="literal">utf8_to_windows_1253</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1253</code></td></tr><tr><td><code class="literal">utf8_to_windows_1254</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1254</code></td></tr><tr><td><code class="literal">utf8_to_windows_1255</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1255</code></td></tr><tr><td><code class="literal">utf8_to_windows_1256</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1256</code></td></tr><tr><td><code class="literal">utf8_to_windows_1257</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN1257</code></td></tr><tr><td><code class="literal">utf8_to_windows_866</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN866</code></td></tr><tr><td><code class="literal">utf8_to_windows_874</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">WIN874</code></td></tr><tr><td><code class="literal">windows_1250_to_iso_8859_2</code></td><td><code class="literal">WIN1250</code></td><td><code class="literal">LATIN2</code></td></tr><tr><td><code class="literal">windows_1250_to_mic</code></td><td><code class="literal">WIN1250</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">windows_1250_to_utf8</code></td><td><code class="literal">WIN1250</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">windows_1251_to_iso_8859_5</code></td><td><code class="literal">WIN1251</code></td><td><code class="literal">ISO_8859_5</code></td></tr><tr><td><code class="literal">windows_1251_to_koi8_r</code></td><td><code class="literal">WIN1251</code></td><td><code class="literal">KOI8R</code></td></tr><tr><td><code class="literal">windows_1251_to_mic</code></td><td><code class="literal">WIN1251</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">windows_1251_to_utf8</code></td><td><code class="literal">WIN1251</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">windows_1251_to_windows_866</code></td><td><code class="literal">WIN1251</code></td><td><code class="literal">WIN866</code></td></tr><tr><td><code class="literal">windows_1252_to_utf8</code></td><td><code class="literal">WIN1252</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">windows_1256_to_utf8</code></td><td><code class="literal">WIN1256</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">windows_866_to_iso_8859_5</code></td><td><code class="literal">WIN866</code></td><td><code class="literal">ISO_8859_5</code></td></tr><tr><td><code class="literal">windows_866_to_koi8_r</code></td><td><code class="literal">WIN866</code></td><td><code class="literal">KOI8R</code></td></tr><tr><td><code class="literal">windows_866_to_mic</code></td><td><code class="literal">WIN866</code></td><td><code class="literal">MULE_INTERNAL</code></td></tr><tr><td><code class="literal">windows_866_to_utf8</code></td><td><code class="literal">WIN866</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">windows_866_to_windows_1251</code></td><td><code class="literal">WIN866</code></td><td><code class="literal">WIN</code></td></tr><tr><td><code class="literal">windows_874_to_utf8</code></td><td><code class="literal">WIN874</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">euc_jis_2004_to_utf8</code></td><td><code class="literal">EUC_JIS_2004</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">utf8_to_euc_jis_2004</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">EUC_JIS_2004</code></td></tr><tr><td><code class="literal">shift_jis_2004_to_utf8</code></td><td><code class="literal">SHIFT_JIS_2004</code></td><td><code class="literal">UTF8</code></td></tr><tr><td><code class="literal">utf8_to_shift_jis_2004</code></td><td><code class="literal">UTF8</code></td><td><code class="literal">SHIFT_JIS_2004</code></td></tr><tr><td><code class="literal">euc_jis_2004_to_shift_jis_2004</code></td><td><code class="literal">EUC_JIS_2004</code></td><td><code class="literal">SHIFT_JIS_2004</code></td></tr><tr><td><code class="literal">shift_jis_2004_to_euc_jis_2004</code></td><td><code class="literal">SHIFT_JIS_2004</code></td><td><code class="literal">EUC_JIS_2004</code></td></tr></tbody><tbody class="footnotes"><tr><td colspan="3"><div id="ftn.id-1.5.8.9.10.2.1.1.1.1" class="footnote"><p><a href="#id-1.5.8.9.10.2.1.1.1.1" class="para"><sup class="para">[a] </sup></a>

  442.           The conversion names follow a standard naming scheme: The

  443.           official name of the source encoding with all

  444.           non-alphanumeric characters replaced by underscores, followed

  445.           by <code class="literal">_to_</code>, followed by the similarly processed

  446.           destination encoding name. Therefore, the names might deviate

  447.           from the customary encoding names.

  448.          </p></div></td></tr></tbody></table></div></div><br class="table-break" /><div class="sect2" id="FUNCTIONS-STRING-FORMAT"><div class="titlepage"><div><div><h3 class="title">9.4.1. <code class="function">format</code></h3></div></div></div><a id="id-1.5.8.9.11.2" class="indexterm"></a><p>

  449.      The function <code class="function">format</code> produces output formatted according to

  450.      a format string, in a style similar to the C function

  451.      <code class="function">sprintf</code>.

  452.     </p><p>

  453. </p><pre class="synopsis">

  454. <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> [, ...] ])

  455. </pre><p>

  456.      <em class="replaceable"><code>formatstr</code></em> is a format string that specifies how the

  457.      result should be formatted.  Text in the format string is copied

  458.      directly to the result, except where <em class="firstterm">format specifiers</em> are

  459.      used.  Format specifiers act as placeholders in the string, defining how

  460.      subsequent function arguments should be formatted and inserted into the

  461.      result.  Each <em class="replaceable"><code>formatarg</code></em> argument is converted to text

  462.      according to the usual output rules for its data type, and then formatted

  463.      and inserted into the result string according to the format specifier(s).

  464.     </p><p>

  465.      Format specifiers are introduced by a <code class="literal">%</code> character and have

  466.      the form

  467. </p><pre class="synopsis">

  468. %[<em class="replaceable"><code>position</code></em>][<em class="replaceable"><code>flags</code></em>][<em class="replaceable"><code>width</code></em>]<em class="replaceable"><code>type</code></em>

  469. </pre><p>

  470.      where the component fields are:

  471. 

  472.      </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>position</code></em> (optional)</span></dt><dd><p>

  473.          A string of the form <code class="literal"><em class="replaceable"><code>n</code></em>$</code> where

  474.          <em class="replaceable"><code>n</code></em> is the index of the argument to print.

  475.          Index 1 means the first argument after

  476.          <em class="replaceable"><code>formatstr</code></em>.  If the <em class="replaceable"><code>position</code></em> is

  477.          omitted, the default is to use the next argument in sequence.

  478.         </p></dd><dt><span class="term"><em class="replaceable"><code>flags</code></em> (optional)</span></dt><dd><p>

  479.          Additional options controlling how the format specifier's output is

  480.          formatted.  Currently the only supported flag is a minus sign

  481.          (<code class="literal">-</code>) which will cause the format specifier's output to be

  482.          left-justified.  This has no effect unless the <em class="replaceable"><code>width</code></em>

  483.          field is also specified.

  484.         </p></dd><dt><span class="term"><em class="replaceable"><code>width</code></em> (optional)</span></dt><dd><p>

  485.          Specifies the <span class="emphasis"><em>minimum</em></span> number of characters to use to

  486.          display the format specifier's output.  The output is padded on the

  487.          left or right (depending on the <code class="literal">-</code> flag) with spaces as

  488.          needed to fill the width.  A too-small width does not cause

  489.          truncation of the output, but is simply ignored.  The width may be

  490.          specified using any of the following: a positive integer; an

  491.          asterisk (<code class="literal">*</code>) to use the next function argument as the

  492.          width; or a string of the form <code class="literal">*<em class="replaceable"><code>n</code></em>$</code> to

  493.          use the <em class="replaceable"><code>n</code></em>th function argument as the width.

  494.         </p><p>

  495.          If the width comes from a function argument, that argument is

  496.          consumed before the argument that is used for the format specifier's

  497.          value.  If the width argument is negative, the result is left

  498.          aligned (as if the <code class="literal">-</code> flag had been specified) within a

  499.          field of length <code class="function">abs</code>(<em class="replaceable"><code>width</code></em>).

  500.         </p></dd><dt><span class="term"><em class="replaceable"><code>type</code></em> (required)</span></dt><dd><p>

  501.          The type of format conversion to use to produce the format

  502.          specifier's output.  The following types are supported:

  503.          </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  504.             <code class="literal">s</code> formats the argument value as a simple

  505.             string.  A null value is treated as an empty string.

  506.            </p></li><li class="listitem"><p>

  507.             <code class="literal">I</code> treats the argument value as an SQL

  508.             identifier, double-quoting it if necessary.

  509.             It is an error for the value to be null (equivalent to

  510.             <code class="function">quote_ident</code>).

  511.            </p></li><li class="listitem"><p>

  512.             <code class="literal">L</code> quotes the argument value as an SQL literal.

  513.             A null value is displayed as the string <code class="literal">NULL</code>, without

  514.             quotes (equivalent to <code class="function">quote_nullable</code>).

  515.            </p></li></ul></div><p>

  516.         </p></dd></dl></div><p>

  517.     </p><p>

  518.      In addition to the format specifiers described above, the special sequence

  519.      <code class="literal">%%</code> may be used to output a literal <code class="literal">%</code> character.

  520.     </p><p>

  521.      Here are some examples of the basic format conversions:

  522. 

  523. </p><pre class="screen">

  524. SELECT format('Hello %s', 'World');

  525. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Hello World</code>

  526. 

  527. SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');

  528. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Testing one, two, three, %</code>

  529. 

  530. SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');

  531. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">INSERT INTO "Foo bar" VALUES('O''Reilly')</code>

  532. 

  533. SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');

  534. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">INSERT INTO locations VALUES('C:\Program Files')</code>

  535. </pre><p>

  536.     </p><p>

  537.      Here are examples using <em class="replaceable"><code>width</code></em> fields

  538.      and the <code class="literal">-</code> flag:

  539. 

  540. </p><pre class="screen">

  541. SELECT format('|%10s|', 'foo');

  542. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       foo|</code>

  543. 

  544. SELECT format('|%-10s|', 'foo');

  545. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>

  546. 

  547. SELECT format('|%*s|', 10, 'foo');

  548. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       foo|</code>

  549. 

  550. SELECT format('|%*s|', -10, 'foo');

  551. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>

  552. 

  553. SELECT format('|%-*s|', 10, 'foo');

  554. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>

  555. 

  556. SELECT format('|%-*s|', -10, 'foo');

  557. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|foo       |</code>

  558. </pre><p>

  559.     </p><p>

  560.      These examples show use of <em class="replaceable"><code>position</code></em> fields:

  561. 

  562. </p><pre class="screen">

  563. SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');

  564. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Testing three, two, one</code>

  565. 

  566. SELECT format('|%*2$s|', 'foo', 10, 'bar');

  567. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       bar|</code>

  568. 

  569. SELECT format('|%1$*2$s|', 'foo', 10, 'bar');

  570. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">|       foo|</code>

  571. </pre><p>

  572.     </p><p>

  573.      Unlike the standard C function <code class="function">sprintf</code>,

  574.      <span class="productname">PostgreSQL</span>'s <code class="function">format</code> function allows format

  575.      specifiers with and without <em class="replaceable"><code>position</code></em> fields to be mixed

  576.      in the same format string.  A format specifier without a

  577.      <em class="replaceable"><code>position</code></em> field always uses the next argument after the

  578.      last argument consumed.

  579.      In addition, the <code class="function">format</code> function does not require all

  580.      function arguments to be used in the format string.

  581.      For example:

  582. 

  583. </p><pre class="screen">

  584. SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');

  585. <em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Testing three, two, three</code>

  586. </pre><p>

  587.     </p><p>

  588.      The <code class="literal">%I</code> and <code class="literal">%L</code> format specifiers are particularly

  589.      useful for safely constructing dynamic SQL statements.  See

  590.      <a class="xref" href="plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE" title="Example 42.1. Quoting Values in Dynamic Queries">Example 42.1</a>.

  591.     </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-math.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-binarystring.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.3. Mathematical Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.5. Binary String Functions and Operators</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1