CODEWITHSUNDEEP
mysql
CodeMonkey
CodeMonkey

Reputation: 174

Substring each row of a column in mySQL

I have a table with a column called menu_name that contains a string like "one|two|three|four|something"

I want to write a one time script that will remove everything after and including the last |

So the final value would be "one|two|three|four"

If there is no | in the cell, i.e "something" then I want to set the string to ""

Thanks!

Thanks to Nanhe Kumar for getting me close, here's the final command: 

UPDATE reports SET menu_name = SUBSTRING(menu_name,1,POSITION(substring_index(menu_name,'|',-1) IN menu_name)-2)

Upvotes: 3

Views: 1414

Answers (2)

Nanhe Kumar
Nanhe Kumar

Reputation: 16297

SELECT SUBSTRING(menu_name,1,POSITION(substring_index(menu_name,'|',-1) IN menu_name)-2) AS menu FROM table_name;

Upvotes: 1

ktm5124
ktm5124

Reputation: 12123

You can do this with the SUBSTRING_INDEX function.

select substring_index(menu_name, '|', 4) from my_table;

To return an empty string in case your delimiter isn't present, you could do a slightly more complicated SELECT:

select if (menu_name like '%|%|%|%|%', substring_index(menu_name, '|', 4), '') 
from my_table;

Here is a SQLfiddle to demonstrate: http://sqlfiddle.com/#!2/30e98/2

Upvotes: 2

Related Questions