Re: How to extract all values except the last value in a string separated by comma in sql

[Johannes Schlüter]

Hi,

thus is the wrong category. The "General" section may be better: https://forums.mysql.com/list.php?20

Anyways:

SUBSTR_INDEX is a possible approach: when using -1 it will return the substring right off the last comma. Using the length of that value and substrcting from the total length we can get the sting up tomthst point:

SELECT SUBSTR(@s, 1, LENGTH(@s)-LENGTH(SUBSTRING_INDEX(@s, ',', -1))-1);

But as a note: If you can, I would suggest to redesign the schema. Having multiple values isn't a good idea. Better is to put those values in their own table. If it has to be inside that table for some reason using JSON might be a good alternative as JSON functions allow some better manipulation of such things over string functions.