mysql - mysql使用带通配符的substring_index从字段中删除不需要的文本

我有一张桌子叫汉堡包。我想清理数据并从汉堡包的名称周围删除一些垃圾文本，仅在标题字段中使用汉堡包的名称。到目前为止，这是我所有的小脑袋都可以鼓起的……

UPDATE hamburgers
SET title = SELECT SUBSTRING_INDEX(title, "My favorite hamburger is called %, I'm not really sure why.   Yesterday was the first time I ate a %", -1) FROM hamburgers
where title like "%My favorite hamburger is called %.%";

我在MySql上很烂，但是我只想找出一种方法来删除汉堡名称周围的文本。大约有3,000个记录与汉堡名称的文字完全相同。 TIA。

最佳答案

这可能使您朝正确的方向前进：

select substring_index(substring_index("My favorite hamburger is called Whopper, I'm not really sure why.   Yesterday was the first time I ate a Whopper","My favorite hamburger is called ",-1),",",1);

这将为您提供：

Whopper

所以：

UPDATE hamburgers
SET title = substring_index(substring_index(title,"My favorite hamburger is called ",-1),",",1)
where title like "My favorite hamburger is called %,";

它确实有点依靠汉堡包后面的逗号。