Home • Explore • Blog • CI
☰



postgresql
44th chunk of `doc/src/sgml/func.sgml`
b8ed2973b3d6ecb03923f94f7060ccae057c24060989467b0000000100000fa0
 at occurrences
        of <parameter>delimiter</parameter> and forms the resulting fields
        into a <type>text</type> array.
        If <parameter>delimiter</parameter> is <literal>NULL</literal>,
        each character in the <parameter>string</parameter> will become a
        separate element in the array.
        If <parameter>delimiter</parameter> is an empty string, then
        the <parameter>string</parameter> is treated as a single field.
        If <parameter>null_string</parameter> is supplied and is
        not <literal>NULL</literal>, fields matching that string are
        replaced by <literal>NULL</literal>.
        See also <link linkend="function-array-to-string"><function>array_to_string</function></link>.
       </para>
       <para>
        <literal>string_to_array('xx~~yy~~zz', '~~', 'yy')</literal>
        <returnvalue>{xx,NULL,zz}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>string_to_table</primary>
        </indexterm>
        <function>string_to_table</function> ( <parameter>string</parameter> <type>text</type>, <parameter>delimiter</parameter> <type>text</type> <optional>, <parameter>null_string</parameter> <type>text</type> </optional> )
        <returnvalue>setof text</returnvalue>
       </para>
       <para>
        Splits the <parameter>string</parameter> at occurrences
        of <parameter>delimiter</parameter> and returns the resulting fields
        as a set of <type>text</type> rows.
        If <parameter>delimiter</parameter> is <literal>NULL</literal>,
        each character in the <parameter>string</parameter> will become a
        separate row of the result.
        If <parameter>delimiter</parameter> is an empty string, then
        the <parameter>string</parameter> is treated as a single field.
        If <parameter>null_string</parameter> is supplied and is
        not <literal>NULL</literal>, fields matching that string are
        replaced by <literal>NULL</literal>.
       </para>
       <para>
        <literal>string_to_table('xx~^~yy~^~zz', '~^~', 'yy')</literal>
        <returnvalue></returnvalue>
<programlisting>
 xx
 NULL
 zz
</programlisting>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>strpos</primary>
        </indexterm>
        <function>strpos</function> ( <parameter>string</parameter> <type>text</type>, <parameter>substring</parameter> <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns first starting index of the specified <parameter>substring</parameter>
        within <parameter>string</parameter>, or zero if it's not present.
        (Same as <literal>position(<parameter>substring</parameter> in
        <parameter>string</parameter>)</literal>, but note the reversed
        argument order.)
       </para>
       <para>
        <literal>strpos('high', 'ig')</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>substr</primary>
        </indexterm>
        <function>substr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <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,
        and extending for <parameter>count</parameter> characters if that is
        specified.  (Same
        as <literal>substring(<parameter>string</parameter>
        from <parameter>start</parameter>
        for <parameter>count</parameter>)</literal>.)
       </para>
       <para>
Title: PostgreSQL String Functions: STRING_TO_TABLE, STRPOS, SUBSTR
Summary
This section describes the PostgreSQL string functions `string_to_table`, which splits a string by a delimiter and returns a set of text rows, `strpos`, which returns the starting index of a substring within a string, and `substr`, which extracts a substring from a string based on a start position and an optional count of characters.