mysql - 如何删除字符串的格式部分？

Question

我有这张表:

// image_path
+----+-----------------------------+
| id |           path              |
+----+-----------------------------+
| 1  | 3498uhrf34i098jf349jf43.png |
| 2  | mfij948t5498hj43h9f34p0.jpg |
| 3  | fcnh9348jmof9348i34f435.png |
| 4  | 3049jr430okf43p0ijfr43f.gif |
| 5  | oi3094jfo439ijf430f443r.jpg |
| 6  | 3f0irj03409iko4p3rfo04f.png |
+----+-----------------------------+

这是我的查询:

SELECT * FROM image_path WHERE path = :token;
//                             ^^^^ removing everything from the right side until dot

举个例子:

SELECT * FROM image_path WHERE path = 'fcnh9348jmof9348i34f435';
/* output:
+----+-----------------------------+
| 3  | fcnh9348jmof9348i34f435.png |
+----+-----------------------------+
*/

我该怎么做？

Answer 1

如何使用 like:

where path like concat('fcnh9348jmof9348i34f435', '.%')

Here是一个 SQL fiddle 。

编辑:

如果你想得到第一个周期之前的部分，那么使用substring_index():

select substring_index(path, '.', 1)