title: emulating MySQL's substring_index() in PGSQL
body: <p>I would like to find an elegant way to emulate the behavior of MySQL's <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index" rel="noreferrer" title="substring_index&#40;&#41;">subtring_index()</a> function in Postgres. </p>
  
  <p>In MySQL, it's as easy as:</p>
  
  <pre><code>mysql&gt; create temporary table test1(test varchar(200));
  Query OK, 0 rows affected (0.01 sec)
  
  mysql&gt; insert into test1 values('apples||oranges'),('apples||grapes');
  Query OK, 2 rows affected (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  
  mysql&gt; select * from test1;
  +-----------------+
  | test            |
  +-----------------+
  | apples||oranges |
  | apples||grapes  |
  +-----------------+
  2 rows in set (0.00 sec)
  
  mysql&gt; select substring_index(test, '||', 1) as field1, substring_index(test, '||', -1) as field2 from test1;
  +--------+---------+
  | field1 | field2  |
  +--------+---------+
  | apples | oranges |
  | apples | grapes  |
  +--------+---------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>But my current work around in PGSQL is quite ugly: </p>
  
  <pre><code>hoth=# create temporary table test1(test text);
  CREATE TABLE
  
  hoth=# insert into test1 values('apples||oranges'),('apples||grapes');
  INSERT 0 2
  
  hoth=# select * from test1;
        test       
  -----------------
   apples||oranges
   apples||grapes
  (2 rows)
  
  hoth=# select substring(test, 0, position('||' in test)) as field1,  substring(test, position('||' in test) + 2, char_length(test)) as field2  from test1;
   field1 | field2  
  --------+---------
   apples | oranges
   apples | grapes
  (2 rows)
  </code></pre>
  
  <p>Perhaps there is a more elegant solution using a regex, or maybe even by splitting the string into an array in a variable which might reduce overhead if the string was derived from a sub-query or something, I welcome any suggestions.</p>