How do I detect the last occurrence of a separator in SUBSTRING

I am pulling out comma-separated values from a string.

Lets say products.additional_images is a string "one,two,three,four,five"

This function will pull out "five" perfectly;

SUBSTRING_INDEX(SUBSTRING_INDEX(products.additional_images,',',5), ',',-1))

However, if I use 6 or 7 in place of 5, I still get "five" because SUBSTRING_INDEX returns the whole string if the index is higher than the number of occurrences.

How do I easily detect the number of occurrences of the comma in the string?

CodePudding user response：

What about you try the following...

select SUBSTRING_INDEX(SUBSTRING_INDEX(products.additional_images,',',length(products.additional_images)-length(replace(products.additional_images,',','')) 1)), ',',-1)