sqlmysqlreplace

Remove space between two words in MySQL

Consider the following example messages in my database:

I dont agree with you
That is something I dont do
This is another string with dont
String without d o n t

Whenever a message has the string " dont" in it, I want to remove the space after it so that it becomes one term with the following word:

I dontagree with you
That is something I dontdo
This is another string with dont
String without d o n t

I have thousands of these kinds of messages in my database. I could query the database in PHP, and then perform a

$message = str_replace(' dont ', ' dont', $message)

but this would probably take a lot more time then in SQL. Is it possible to make the same operation in SQL?

Solution

  • Use the SQL replace() function:

    update mytable set
mycol = replace(mycol, ' dont ', ' dont')

    You are right - it would be a lot faster in SQL