MySQL分解字符串并删除最后一部分

Question

我有类似的字符串

some1-some2-som3-123
some1-some2-143
some1-1235

如何删除最后一部分-123？从带有sql语句的字符串

Answer 1

您可以从开始到最后使用简单的SUBSTRING -:

SELECT col, SUBSTRING(col, 0, LENGTH(col) - INSTR(REVERSE(col), '-')) AS result
FROM tab;

SqlFiddleDemo

输出：

╔═══════════════════════╦══════════════════╗
║         col           ║      result      ║
╠═══════════════════════╬══════════════════╣
║ some1-some2-som3-123  ║ some1-some2-som3 ║
║ some1-some2-143       ║ some1-some2      ║
║ some1-1235            ║ some1            ║
╚═══════════════════════╩══════════════════╝

Answer 2

演示

首先查找"-"的最后一个索引

SELECT LENGTH("first-middle-last") - LOCATE('-', REVERSE("first-middle-last"));

然后提取左侧部分-

SELECT LEFT("first-middle-last", LENGTH("first-middle-last") - LOCATE('-', REVERSE("first-middle-last")));