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.
Tim Biegeleisen :
Here is one trick do this, making use of the REVERSE function:\n\nUPDATE yourTable\nSET col =\n REVERSE(SUBSTRING(REVERSE(col), INSTR(REVERSE(col), '.')))\n\n\nDemo\n\nThe 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.",
2019-02-22T06:39:37
Gordon Linoff :
I wouldn't use reverse(). Instead:\n\nUPDATE table1\n SET Col1 = LEFT(col1, LENGTH(col1) - LENGTH(SUBSTRING_INDEX(Col1, '.', -1)) - 1);\n",
2019-02-22T12:48:05