I have a MySQL table with VARCHAR field description. Here's an example value in this field:

The quick brown fox jumps over the lazy dogThis is the second sentence.

I want to change the text so that there is a period and space between the two sentences. I think I can fix this with regex, but I don't know how to use the regex in SQL.

How can I use regex to update a field in MySQL?

4

2 Answers

If you use MariaDB (most of MySQL is compatible with MariaDB). Then you can use regular expression and also back reference. An example is given in documentation.

SELECT REGEXP_REPLACE('James Bond','^(.*) (.*)$','\\2, \\1') AS reorder_name;

Gives out as

reorder_name as Bond, James

If you want to search in a field; whose data is multiline then above search will not work. For this you have to first change the system variable default_regex_flags. i.e.

SET GLOBAL default_regex_flags="DOTALL,MULTILINE";
SET SESSION default_regex_flags="DOTALL,MULTILINE";

FYI: By default this system variable is empty.

This question has been answered on StackOverflow ():

No.

But if you have access to your server, you could use a user defined function (UDF) like mysql-udf-regexp.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy