CODEWITHSUNDEEP
mysqlstring
Jam Dos
Jam Dos

Reputation: 149

MySQL split string and get all substring except the last

I want to split the string in MySQL query using a delimiter. However, I want to split on the last occurrence of the string and get the first part of the string.

For example:-

'apple - banana - grape'

The result after splitting should be 'apple - banana'. The important thing is that we do not know how many occurrences of '-' will be there in the string.

Upvotes: 0

Views: 724

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

On MySQL 8+, the REGEXP_REPLACE function works well for your requirement:

SELECT fruits, REGEXP_REPLACE(fruits, '\\s*-\\s*[^-]+$', '') AS fruits_out
FROM yourTable;

Demo

By the way, a much better table design would be to store each CSV fruit value in a separate row. This would alleviate the need to use regex to manipulate the list of fruits.

Upvotes: 1

Related Questions