Update MySQL regular expression

How to update MySQL rows using regular expressions. Here is a snapshot of my table that needs updating:

I suppose I would use REGEX for pattern matching but I’m not certain how to update remove or delete the first portion ‘/dev/’.

MySQL rows that need updating using regular expressions

MySQL rows that need updating using regular expressions

What I ended up doing was using the MySQL REPLACE. According to the MySQL definition this is what the REPLACE function does:

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

So, in my case, I used the following:

UPDATE skincar3_cosmetic.change_pic SET picture=REPLACE(picture, '/dev', '') WHERE change_pic_id>=1;

This is the snapshot of the above MySQL executed code:

Result of the MySQL REPLACE function

Result of the MySQL REPLACE function

I suppose I could use the SUBSTR() function (aka SUBSTRING() function):

According to the MySQL documentations:

SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)

SUBSTR() is a synonym for SUBSTRING().

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

Reference from MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Share and Enjoy:
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Share

You must be logged in to post a comment.