Use regular expression to update text in MySQL field

Question

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?

Solution

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.

Comments

[agentmg123]

This is just in one field, the objective is to put a period and space between those two sentences. In the current example, the end of first sentence and the beginning of the second sentence have nothing in between to separate them dogThis.

[agentmg123]

I used VARCHAR for this description field. I want both sentences to be saved in the same field but I just want them to be separated with a period and a space. I have not used any SQL, I'm not even sure if it's possible to use SQL statements on this one. Do you know of any other solutions that I can use to crack it?

[Vomit IT - Chunky Mess Style]

See this SQL i.imgur.com/SAc29ag.png that gives the expected result from the same field but I don't have time to build an UPDATE statement and make dynamic. I don't do a lot of SQL updates personally and use INSERT statements more but there may be better ways to do this with stored procs, passing arguments, return values, etc. but this was a quick test to confirm it is possible. Building the dynamic UPDATE statement isn't something I have time to work on right now, see if this is anything you can work with. But you will need: github.com/mysqludf/lib_mysqludf_preg/downloads

[Vomit IT - Chunky Mess Style]

If you don't get enough attention here with this or this doesn't help you, consider asking a moderator to migrate over to stackoverflow.com as there are some really good MySQL query writers there that I've asked for help so many more SQL guys over there for sure. Don't cross-post though and ask for this to be migrated. I'm going to delete my answer since I misunderstood what you were trying to accomplish. See here for the lib_mysqludf_preg install instruction if you're running Windows OS as this helped me some with the error.