Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Tuesday 21 May 2013

Remove Carriage return in varchar column in sql

Issue

I have a mysql table with VARCHAR column which contains String values. Sometimes at the end of the string there is a carriage return (\r). I only want to delete the \r at the end of the string if it exists.

Solution

A carriage return is CHAR(13)

The following code will remove Carriage return 

UPDATE table_name set column_name=REPLACE(column_name,char(13),'')

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);



Trim all the values in a column in a sql

Trim all the values in a column in a sql

SQL does not have a trim function. You'll need to use RTRIM and LTRIM together.
update MyTable set Name = RTRIM(LTRIM((name))