我希望找到一种优雅的方法来模拟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)
也许可以使用正则表达式来寻找更优雅的解决方案,或者甚至通过将字符串分割成数组并存储在一个变量中来减少开销(如果字符串是从子查询或其他什么地方生成的),我欢迎任何建议。