I would like to find an elegant way to emulate the behavior of MySQL's subtring_index() function in Postgres.
In MySQL, it's as easy as:
mysql> create temporary table test1(test varchar(200));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values('apples||oranges'),('apples||grapes');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test1;
+-----------------+
| test |
+-----------------+
| apples||oranges |
| apples||grapes |
+-----------------+
2 rows in set (0.00 sec)
mysql> 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)
But my current work around in PGSQL is quite ugly:
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)
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.
Always take the time to skim the manuals.
http://www.postgresql.org/docs/current/static/functions-string.html
If split_part(string text, delimiter text, field int)
doesn't do what you want (and more, if I understand your MySQL function) then you'll need to explain where and why.
Here is how I implement (or emulate) MySQL's subtring_index() in PostgreSQL
CREATE OR REPLACE FUNCTION public.substring_index (
str text,
delim text,
count integer = 1,
out substring_index text
)
RETURNS text AS
$body$
BEGIN
IF count > 0 THEN
substring_index = array_to_string((string_to_array(str, delim))[:count], delim);
ELSE
DECLARE
_array TEXT[];
BEGIN
_array = string_to_array(str, delim);
substring_index = array_to_string(_array[array_length(_array, 1) + count + 1:], delim);
END;
END IF;
END;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 5;
and here is the example from mysql's documentation;
postgres=# SELECT substring_index('www.mysql.com', '.', 2);
substring_index
-----------------
www.mysql
(1 row)
postgres=# SELECT substring_index('www.mysql.com', '.', -2);
substring_index
-----------------
mysql.com
(1 row)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With