Translating SUBSTRING_INDEX() in MySQL to PostgreSQL

Question

There are a number of question related to this and the answer is to use split_part(). For example:

emulating MySQL's substring_index() in PGSQL

Mysql`s SUBSTRING_INDEX equivalent in postgresql

I'm not getting the same behavior, however. I'm trying to figure out how to get the following functionality in Postgres.

If you have a string that looks like:

+------------------------------------------+
| string                                   |
+------------------------------------------+
| A_123, B_123, C_123, D_123, E_123, F_123 |
+------------------------------------------+

MySQL will return the following with the given statement:

mysql> select SUBSTRING_INDEX(string, ',', 4) AS test FROM tbl;
+----------------------------+
| test                       |
+----------------------------+
| A_123, B_123, C_123, D_123 |
+----------------------------+

PostgreSQL will return the following with the given statement:

mysql> select split_part(string, ',', 4) AS test FROM tbl;
+-------+
| test  |
+-------+
| D_123 |
+-------+

Is there a similar function or just implementing a function like this?

Do you have a chance to fix your data model during the migration and store the data in a properly normalized way? — user330315
– user330315, Commented Mar 30, 2020 at 5:26
I don't see any other solution that writing your own function. Or use something like array_to_string((regexp_split_to_array(string, '\s*,\s'))[:4], ', ') - An alternative might be to convert this to a real array (if you do need to keep this de-normalized) and access array slices. — user330315
– user330315, Commented Mar 30, 2020 at 6:19
@a_horse_with_no_name Thanks, that (the regex expression) returned the desired result! When you get a chance, can you post the answer so I can accept it? — cheslijones
– cheslijones, Commented Mar 30, 2020 at 15:56

cheslijones · Accepted Answer · 2020-04-05 00:10:09Z

1

As a_horse_with_no_name suggested in the comments, this had the desired result:

array_to_string((regexp_split_to_array(string, '\s*,\s'))[:4], ', ')

answered Apr 5, 2020 at 0:10

cheslijones

9,30425 gold badges103 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Translating SUBSTRING_INDEX() in MySQL to PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related