substring_index in replace mysql

Question

假设我有这样一个句子

id    sentence
1. I LOVE ORANGE 300G, and i want that Orange 300G
2. I HATE STRAWBERRY 500G, and i want that Strawberry 500G
3. i love the orange 300G (with that big), and i want that orange 300G (with that big)

我想删除每个句子的最后一个索引，所以预期的结果应该是这样的

I LOVE ORANGE 300G, and i want that Orange
I HATE STRAWBERRY 500G, and i want that Strawberry
i love the orange 300G (with that big), and i want that orange (with that big)

i've try with this

SELECT REPLACE(mytable.sentence, substring_index(mytable.sentence, '', -1), '') AS meta_keywords2 FROM mytable

但是由于在一个句子中最后一个索引总是与另一个索引相同，所以结果是这样的:

I LOVE ORANGE, and i want that Orange
I HATE STRAWBERRY, and i want that Strawberry

Answer 1

我有个想法:

SELECT SUBSTRING(mytable.sentence, 1, LENGTH(mytable.sentence)-LENGTH(SUBSTRING_INDEX(mytable.sentence, ' ', -1))) AS meta_keywords2 
FROM mytable;

Here's a fiddle

1. 获取要提取的句子中最后一个单词的LENGTH()。
2. 获取sentence的总LENGTH()。
3. 对起始位置为1的sentence使用SUBSTRING()，提取总长度与最后一个字长之间的总字符差。