Use Regular Expression to Update Text in Mysql Field
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...
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?
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.
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
