CODEWITHSUNDEEP
mysqlsql
Wizard
Wizard

Reputation: 11265

MySQL explode string and remove last part

I have string like 

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

How I can remove last part -123 ? from that string with sql statment

Upvotes: 2

Views: 662

Answers (2)

shakhawat
shakhawat

Reputation: 2727

DEMO

First find the last index of '-'

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

Then extract the left part - 

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175576

You can use simple SUBSTRING from beginning to last -:

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

SqlFiddleDemo

Output:

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

Upvotes: 3

Related Questions