1

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?

cjones
  • 8,384
  • 17
  • 81
  • 175
  • Do you have a chance to fix your data model during the migration and store the data in a properly normalized way? –  Mar 30 '20 at 05:26
  • 1
    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. –  Mar 30 '20 at 06: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? – cjones Mar 30 '20 at 15:56

1 Answers1

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], ', ')
cjones
  • 8,384
  • 17
  • 81
  • 175