在PGSQL中模拟MySQL的substring_index() [英] emulating MySQL's substring_index() in PGSQL

问题描述

我想找到一种优雅的方法来模拟MySQL的 subtring_index()函数.

I would like to find an elegant way to emulate the behavior of MySQL's subtring_index() function in Postgres.

在MySQL中，它很简单:

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)

但是我目前在PGSQL方面的工作非常丑陋:

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.

推荐答案

总是花时间浏览手册.

http://www.postgresql.org/docs/current/static/functions-string .html

如果split_part(string text, delimiter text, field int)不能满足您的要求(并且，如果我了解您的MySQL函数，那么还有更多)，那么您需要说明在哪里以及为什么.

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.

这篇关于在PGSQL中模拟MySQL的substring_index()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！