How to get everything before the last occurrence of a character in MySQL?

Question

I want to get everything before the last occurrence of a specific character from a column.

SUBSTRING_INDEX with negative value works well for the separation but return the part of the string I want to delete. For example, consider a column as

first. second. third. fourth

SUBSTRING_INDEX(Col1, '.', -1) returns fourth, but I want to get

first. second. third.

In fact, I want to update the same col by removing anything after the last occurrence of .. In other words, I want to remove SUBSTRING_INDEX(Col1, '.', -1) part, but I cannot simply use REPLACE as

UPDATE table1 SET Col1=REPLACE(Col1,SUBSTRING_INDEX(Col1, '.', -1),'')

because it may occur in other parts of the string.

Answer 1

Here is one trick do this, making use of the REVERSE function:

UPDATE yourTable
SET col =
    REVERSE(SUBSTRING(REVERSE(col), INSTR(REVERSE(col), '.')))

Demo

The idea here is to reverse the string, then use INSTR to find the position of the last (now first) dot. Then, we substring from the position to the beginning, and then reverse again.

Answer 2

I wouldn't use reverse(). Instead:

UPDATE table1
    SET Col1 = LEFT(col1, LENGTH(col1) - LENGTH(SUBSTRING_INDEX(Col1, '.', -1)) - 1);

Answer 3

YOu can use left() and locate() funtion

DEMO

UPDATE table1 SET Col1= left(col1,
locate(SUBSTRING_INDEX(col1, '.', -1),col1))

OUTPUT:

val
first. second. third.