func.sgml

PostgreSQL7.4.6 for Linux

      <row>
       <entry><literal><function>atan</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>inverse tangent</entry>
      </row>

      <row>
       <entry><literal><function>atan2</function>(<replaceable>x</replaceable>,
        <replaceable>y</replaceable>)</literal></entry>
       <entry>inverse tangent of
        <literal><replaceable>x</replaceable>/<replaceable>y</replaceable></literal></entry>
      </row>

      <row>
       <entry><literal><function>cos</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>cosine</entry>
      </row>

      <row>
       <entry><literal><function>cot</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>cotangent</entry>
      </row>

      <row>
       <entry><literal><function>sin</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>sine</entry>
      </row>

      <row>
       <entry><literal><function>tan</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>tangent</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  </sect1>


  <sect1 id="functions-string">
   <title>String Functions and Operators</title>

   <para>
    This section describes functions and operators for examining and
    manipulating string values.  Strings in this context include values
    of all the types <type>character</type>, <type>character
     varying</type>, and <type>text</type>.  Unless otherwise noted, all
    of the functions listed below work on all of these types, but be
    wary of potential effects of the automatic padding when using the
    <type>character</type> type.  Generally, the functions described
    here also work on data of non-string types by converting that data
    to a string representation first.  Some functions also exist
    natively for the bit-string types.
   </para>

   <para>
    <acronym>SQL</acronym> defines some string functions with a special syntax where
    certain key words rather than commas are used to separate the
    arguments.  Details are in <xref linkend="functions-string-sql">.
    These functions are also implemented using the regular syntax for
    function invocation.  (See <xref linkend="functions-string-other">.)
   </para>

   <table id="functions-string-sql">
    <title><acronym>SQL</acronym> String Functions and Operators</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>  
      </row>
     </thead>

     <tbody>
      <row>
       <entry><literal><parameter>string</parameter> <literal>||</literal>
        <parameter>string</parameter></literal></entry>
       <entry> <type>text</type> </entry>
       <entry>
        String concatenation
        <indexterm>
         <primary>character string</primary>
         <secondary>concatenation</secondary>
        </indexterm>
       </entry>
       <entry><literal>'Post' || 'greSQL'</literal></entry>
       <entry><literal>PostgreSQL</literal></entry>
      </row>

      <row>
       <entry><literal><function>bit_length</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>integer</type></entry>
       <entry>Number of bits in string</entry>
       <entry><literal>bit_length('jose')</literal></entry>
       <entry><literal>32</literal></entry>
      </row>

      <row>
       <entry><literal><function>char_length</function>(<parameter>string</parameter>)</literal> or <literal><function>character_length</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>integer</type></entry>
       <entry>
        Number of characters in string
        <indexterm>
         <primary>character string</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary sortas="character string">of a character string</secondary>
         <see>character strings, length</see>
        </indexterm>
       </entry>
       <entry><literal>char_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry><literal><function>convert</function>(<parameter>string</parameter>
       using <parameter>conversion_name</parameter>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Change encoding using specified conversion name.  Conversions
        can be defined by <command>CREATE CONVERSION</command>.  Also
        there are some pre-defined conversion names. See <xref
        linkend="conversion-names"> for available conversion
        names.
       </entry>
       <entry><literal>convert('PostgreSQL' using iso_8859_1_to_utf_8)</literal></entry>
       <entry><literal>'PostgreSQL'</literal> in Unicode (UTF-8) encoding</entry>
      </row>

      <row>
       <entry><literal><function>lower</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Convert string to lower case</entry>
       <entry><literal>lower('TOM')</literal></entry>
       <entry><literal>tom</literal></entry>
      </row>

      <row>
       <entry><literal><function>octet_length</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>integer</type></entry>
       <entry>Number of bytes in string</entry>
       <entry><literal>octet_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry><literal><function>overlay</function>(<parameter>string</parameter> placing <parameter>string</parameter> from <type>integer</type> <optional>for <type>integer</type></optional>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Replace substring
        <indexterm>
         <primary>overlay</primary>
        </indexterm>
       </entry>
       <entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>
       <entry><literal>Thomas</literal></entry>
      </row>

      <row>
       <entry><literal><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</literal></entry>
       <entry><type>integer</type></entry>
       <entry>Location of specified substring</entry>
       <entry><literal>position('om' in 'Thomas')</literal></entry>
       <entry><literal>3</literal></entry>
      </row>

      <row>
       <entry><literal><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>
       <entry><literal>hom</literal></entry>
      </row>

      <row>
       <entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring matching POSIX regular expression
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from '...$')</literal></entry>
       <entry><literal>mas</literal></entry>
      </row>

      <row>
       <entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable> for <replaceable>escape</replaceable>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring matching <acronym>SQL</acronym> regular
        expression
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from '%#"o_a#"_' for '#')</literal></entry>
       <entry><literal>oma</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>trim</function>(<optional>leading | trailing | both</optional>
        <optional><parameter>characters</parameter></optional> from
        <parameter>string</parameter>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only the
        <parameter>characters</parameter> (a space by default) from the
        start/end/both ends of the <parameter>string</parameter>.
       </entry>
       <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>
       <entry><literal>Tom</literal></entry>
      </row>

      <row>
       <entry><literal><function>upper</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Convert string to upper case</entry>
       <entry><literal>upper('tom')</literal></entry>
       <entry><literal>TOM</literal></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Additional string manipulation functions are available and are
    listed in <xref linkend="functions-string-other">.  Some of them are used internally to implement the
    <acronym>SQL</acronym>-standard string functions listed in <xref linkend="functions-string-sql">.
   </para>

   <table id="functions-string-other">
    <title>Other String Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><literal><function>ascii</function>(<type>text</type>)</literal></entry>
       <entry>integer</entry>
       <entry><acronym>ASCII</acronym> code of the first character of the argument</entry>
       <entry><literal>ascii('x')</literal></entry>
       <entry><literal>120</literal></entry>
      </row>

      <row>
       <entry><literal><function>btrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>characters</parameter> <type>text</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string consisting only of characters
        in <parameter>characters</parameter> from the start and end of
        <parameter>string</parameter>.
       </entry>
       <entry><literal>btrim('xyxtrimyyx', 'xy')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><literal><function>chr</function>(<type>integer</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Character with the given <acronym>ASCII</acronym> code</entry>
       <entry><literal>chr(65)</literal></entry>
       <entry><literal>A</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>convert</function>(<parameter>string</parameter>
        <type>text</type>,
        <optional><parameter>src_encoding</parameter> <type>name</type>,</optional>
        <parameter>dest_encoding</parameter> <type>name</type>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Convert string to <parameter>dest_encoding</parameter>.
        The original encoding is specified by
        <parameter>src_encoding</parameter>.  If
        <parameter>src_encoding</parameter> is omitted, database
        encoding is assumed.
       </entry>
       <entry><literal>convert( 'text_in_unicode', 'UNICODE', 'LATIN1')</literal></entry>
       <entry><literal>text_in_unicode</literal> represented in ISO 8859-1 encoding</entry>
      </row>

      <row>
       <entry>
        <literal><function>decode</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>type</parameter> <type>text</type>)</literal>
       </entry>
       <entry><type>bytea</type></entry>
       <entry>
        Decode binary data from <parameter>string</parameter> previously 
        encoded with <function>encode</>.  Parameter type is same as in <function>encode</>.
       </entry>
       <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
       <entry><literal>123\000\001</literal></entry>
      </row>       

      <row>
       <entry>
        <literal><function>encode</function>(<parameter>data</parameter> <type>bytea</type>,
        <parameter>type</parameter> <type>text</type>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Encode binary data to <acronym>ASCII</acronym>-only representation.  Supported
        types are: <literal>base64</>, <literal>hex</>, <literal>escape</>.
       </entry>
       <entry><literal>encode( '123\\000\\001', 'base64')</literal></entry>
       <entry><literal>MTIzAAE=</literal></entry>
      </row>       

      <row>
       <entry><literal><function>initcap</function>(<type>text</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Convert first letter of each word (whitespace-separated) to upper case</entry>
       <entry><literal>initcap('hi thomas')</literal></entry>
       <entry><literal>Hi Thomas</literal></entry>
      </row>

      <row>
       <entry><literal><function>length</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>integer</type></entry>
       <entry>
        Number of characters in string
        <indexterm>
         <primary>character string</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary sortas="character string">of a character string</secondary>
         <see>character strings, length</see>
        </indexterm>
       </entry>
       <entry><literal>length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>lpad</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>integer</type>
        <optional>, <parameter>fill</parameter> <type>text</type></optional>)</literal>
       </entry>
       <entry>text</entry>
       <entry>
        Fill up the <parameter>string</parameter> to length
        <parameter>length</parameter> by prepending the characters
        <parameter>fill</parameter> (a space by default).  If the
        <parameter>string</parameter> is already longer than
        <parameter>length</parameter> then it is truncated (on the
        right).
       </entry>
       <entry><literal>lpad('hi', 5, 'xy')</literal></entry>
       <entry><literal>xyxhi</literal></entry>
      </row>

      <row>
       <entry><literal><function>ltrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>characters</parameter> <type>text</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only characters from
        <parameter>characters</parameter> from the start of the string.
       </entry>
       <entry><literal>ltrim('zzzytrim', 'xyz')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>