Remove First Part of a Column Value

Let's say you have some values in your MySQL database that look like this:​

a_180.0/v1355635866/r0y0ajbjiqry42tqscof.jpg

You want to to remove the first bit in a bulk process (the a_180.0/) and you don't want to have to write a 10 line Python script.  Well then, do it in good 'ol SQL.​

Table: event_photo, Column: original_image_id

 UPDATE event_photo SET original_image_id = TRIM(LEADING 'a_180.0/' FROM original_image_id);​

If you want to remove the .jpg part of the column value you can do this:

UPDATE event_photo SET original_image_id = TRIM(TRAILING '.jpg' FROM original_image_id);​

If you have spaces in the front or back of the value and you want to remove those just do this:​

UPDATE event_photo SET original_image_id = TRIM(original_image_id);

sed Substitutions

Substitute (find and replace) "good" with "bad" on each line

 sed 's/good/bad/'            + replaces only 1st instance in a line

 sed 's/good/bad/5'           + replaces only 5th instance in a line
 sed 's/good/bad/g'           + replaces all instances in a line
 sed 's/\(.*\)good\(.*good\)/\1bad\2/' + replace the next-to-last case
 sed 's/\(.*\)good/\1bad/'            + replace only the last case

Substitute "good" with "bad" only for lines which contain "bla"
 sed '/bla/s/good/bad/g'

​Substitute "good" with "bad" except for lines which contain "bla"

 sed '/bla/!s/good/bad/g'

Set MySQL root password to NULL

​Not recommended but if needed...

From the command line

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -uroot

From MySQL

use mysql;
update user set password=null where User='root';
flush privileges;
quit;