Tuesday, 21 May 2013

Remove a character from a string in mysql

Issue 

I have a mysql table with a column name "sequence". sequence column contains protein sequence starting with "protein name > sequence letters"
I need to remove the characters left to ">" including >

for eg: for the input sequence 
Influenza A virus > MKAKLLVLLCAFTATYA
the output should be
MKAKLLVLLCAFTATYA

Solution

You can use SUBSTRING_INDEX(str,delim,count

SUBSTRING_INDEX returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

you can use

update table_name set column_name = SUBSTRING_INDEX(column_name, '>', -1);