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

Databases
1

I am think of SUBSTRING_INDEX but dont know how to extract all values except the last value in a string separated by comma

e.g :
value is “aaa,bbb,ccc,ddd”
desired result is “aaa,bbb,ccc”

e.g :
value is “a1,b2,c3,d4,e5”
desired result is “a1,b2,c3,d4”

2

( * Not sure what you are actually doing, but multiple pieces of data should not be stored in the same column.)

There are a couple ways to do this.

SELECT TRIM(TRAILING ',' FROM SUBSTRING_INDEX('aaa,bbb,ccc,ddd', ',', LENGTH('aaa,bbb,ccc,ddd') - LENGTH(REPLACE('aaa,bbb,ccc,ddd', ',', '')))) AS result;
SELECT TRIM(TRAILING ',' FROM SUBSTRING_INDEX('a1,b2,c3,d4,e5', ',', LENGTH('a1,b2,c3,d4,e5') - LENGTH(REPLACE('a1,b2,c3,d4,e5', ',', '')))) AS result;

or

SELECT SUBSTRING('aaa,bbb,ccc,ddd', 1, LENGTH('aaa,bbb,ccc,ddd') - LOCATE(',', REVERSE('aaa,bbb,ccc,ddd'))) AS result;
SELECT SUBSTRING('a1,b2,c3,d4,e5', 1, LENGTH('a1,b2,c3,d4,e5') - LOCATE(',', REVERSE('a1,b2,c3,d4,e5'))) AS result;
3 Likes
3

It works. Txs Appreciated.

5

Or you could just… handle it when you get it out of the database.
Or not have it in the database at all, because that is a silly way to store data.

6

a most disheartening comment for people who are tasked with returning results from a database they had no hand in creating and which they cannot change

“you have meddled with the the primal forces of nature… and you will atone!”

1 Like
7

Ah, so they’re incapable to create their own, properly formatted database, and fill it from the extant badly formed one?

I see.

8

you will atone!!

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.