[FIXED] How can i replace numerical value (version) using MYSQL?

Issue

I am building a Document versioning intranet website. I have around 1700 book title names which includes version like

• Book for Dummies 1.2
• Testbook HTML5 Class 3.5.7 students
• Learning Amazon S3 Deployment 1.3.4.3 writings

How can i remove document version x.x.x or x.x from all the document title names.

As i want to publish a list of all Title without version names on front page.

I tried REPLACE function in MYSQL replacing dots, but it also replaced 3 in S3 and 5 in HTML5.

update book_title_all set title=REGEXP_REPLACE(title,'[0-9]','')
update book_title_all set title=REPLACE(title,'.','')

I want that only numerical string in the format x.x.x / x.x only be replaced.

Solution

If you are running MySQL 8+, then the REGEXP_REPLACE function comes in handy here:

UPDATE book_title_all
SET title = TRIM(REGEXP_REPLACE(title, '\s*[0-9]+(\.[0-9]+)+\s*', ' '))
WHERE title REGEXP '[0-9]+\.[0-9]+';

The idea here is to replace every version string 1.2.3.4, along with optional whitespace surrounding it, with just a single space. This keeps the formatting of the title clean after the replacement. Note that this creates an edge case, where a version string appears at the very beginning or end of the title. In this case, our replacement would leave an extra space. The call to TRIM() is needed to remove this.

Answered By - Tim Biegeleisen