Home • Explore • Blog • CI
☰



postgresql
32th chunk of `doc/src/sgml/func.sgml`
81532cb153c9068c6d6b694325df73dd197bbadc1a497a5c0000000100000fbc
 <returnvalue>test</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>substring</primary>
        </indexterm>
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Extracts the substring of <parameter>string</parameter> starting at
        the <parameter>start</parameter>'th character if that is specified,
        and stopping after <parameter>count</parameter> characters if that is
        specified.  Provide at least one of <parameter>start</parameter>
        and <parameter>count</parameter>.
       </para>
       <para>
        <literal>substring('Thomas' from 2 for 3)</literal>
        <returnvalue>hom</returnvalue>
       </para>
       <para>
        <literal>substring('Thomas' from 3)</literal>
        <returnvalue>omas</returnvalue>
       </para>
       <para>
        <literal>substring('Thomas' for 2)</literal>
        <returnvalue>Th</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Extracts the first substring matching POSIX regular expression; see
        <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>substring('Thomas' from '...$')</literal>
        <returnvalue>mas</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>SIMILAR</literal> <parameter>pattern</parameter> <type>text</type> <literal>ESCAPE</literal> <parameter>escape</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para role="func_signature">
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> <literal>FOR</literal> <parameter>escape</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Extracts the first substring matching <acronym>SQL</acronym> regular expression;
        see <xref linkend="functions-similarto-regexp"/>.  The first form has
        been specified since SQL:2003; the second form was only in SQL:1999
        and should be considered obsolete.
       </para>
       <para>
        <literal>substring('Thomas' similar '%#"o_a#"_' escape '#')</literal>
        <returnvalue>oma</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>trim</primary>
        </indexterm>
        <function>trim</function> ( <optional> <literal>LEADING</literal> | <literal>TRAILING</literal> | <literal>BOTH</literal> </optional>
        <optional> <parameter>characters</parameter> <type>text</type> </optional> <literal>FROM</literal>
        <parameter>string</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Removes the longest string containing only characters in
        <parameter>characters</parameter> (a space by default) from the
        start, end, or both ends (<literal>BOTH</literal> is the default)
        of <parameter>string</parameter>.
       </para>
       <para>
        <literal>trim(both 'xyz' from 'yxTomxx')</literal>
        <returnvalue>Tom</returnvalue>
Title: PostgreSQL String Functions: substring (continued) and trim
Summary
This section details the different variations of the `substring` function in PostgreSQL, which allows extracting substrings using character positions or regular expressions (POSIX and SQL). It also covers the `trim` function, which removes leading, trailing, or both leading and trailing characters from a string.