1

i need to use a mysql update query with regex so my example is

update table_name set coulmn_name = replace(coulmn_name, 'regex', '');

i will explain a specified thing i need to do. my column is contained some text & some html tags i need to delete all img tags from this coulmn what can i do to achieve this ?

this is my ReGex

/</?(?i:img)(.|\n)*?>/g

but i don't know how to make this any help please

3
  • 2
    MySQL does not support RegEx for replacing.
    – user1822
    Sep 19, 2013 at 15:57
  • ok, i will explain what a specified i need to do. my column is contained some text & some html tags i need to delete all <img> tags from this coulmn what can i do to achieve this ?
    – adnan
    Sep 19, 2013 at 16:02

1 Answer 1

0

MySQL doesn't support it natively, but this is possible if you install the user-defined functions from the lib_mysqludf_preg library.

You should be able to then do something like this:

UPDATE table_name
   SET column_name = PREG_REPLACE('regex_match', 'replacement_value', column_name);

This is third-party software but it uses the standard MySQL User-defined function interface to enable custom functions written in C/C++ to interact and behave like built-in functions (e.g., REPLACE()) ... but UDFs are essentially plug-ins that can be installed and uninstalled without having to recompile the server or even having to shut it down and restart.

Above, the 'regex_match' and 'replacement_value' are the regular expression and replacement values, which can be from columns, variables, or supplied in the query as string literals. In the last case, they need to be quoted.

8
  • ok, i have a root access but my server is underpowered. so does this module will use alot of memory? & if i installed this module where is i put this query in phpmyadmin or another place?
    – adnan
    Sep 19, 2013 at 17:44
  • It should use very little memory. While a query using the function is actually in the process of running, it will probably be CPU-intensive, not memory. You "put" (execute) the query wherever you would normally execute any other query -- once installed, this becomes another function you can use in any query, not really any different than the built-in functions like REPLACE() or CONCAT() or SUBSTRING_INDEX() or any other string function. Sep 20, 2013 at 1:46
  • thanks Michael could you give me a step by step guide to install this plugin please? ... my server is CentOS 64 bit
    – adnan
    Sep 22, 2013 at 5:31
  • A step-by-step tutorial is outside our scope, here. The link to the project has a README file, which states that the installation instructions are found in a file called INSTALL. Sep 22, 2013 at 17:09
  • ok, thanks for your help. what about unistall?
    – adnan
    Sep 23, 2013 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.