Query to delete all characters after the last comma in a string

Question

I have a mysql table with such data

TACOMA, Washington, 98477 

Now I have thousands of such lines. I want the data to be processed in such a way that it looks like this:

 TACOMA, Washington 

Is this possible, though mysql or I need to do this manually.

Answer 1

You can use:

 SELECT SUBSTRING_INDEX('TACOMA, Washington, 98477', ',', 2) 

You can read it here.

And the update statement:

 UPDATE my_table SET my_col = SUBSTRING_INDEX(my_col, ',', 2) 

If you need to replace my_table with the table name and my_col with the column that needs to be updated.

Answer 2

Perhaps so. Count the number of commas (by checking the length in length, removing all commas), and then use SUBSTRING_INDEX to get the string to the number of commas: -

 SELECT SUBSTRING_INDEX(col, ',', LENGTH(col) - LENGTH(REPLACE(col, ',', ''))) FROM SomeTable 

Answer 3

 substring_index(col, ',',-1) 

will give a string from the last index to a comma

 replace(col,concat(',',substring_index(col, ',',-1)),'')