mysql index sub part_在PGSQL中模拟MySQL的substring_index()

我想找到一种优雅的方式来模拟Postgres中

MySQL

subtring_index()函数的行为.

在MySQL中,它很简单：

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)

但我目前在PGSQL中的工作非常难看：

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)

也许使用正则表达式有一个更优雅的解决方案,或者甚至可以将字符串拆分成变量中的数组,如果字符串是从子查询或其他东西派生的话,这可能会减少开销,我欢迎任何建议.