How to search for 3 lettered words in a column - MySQL?

Question

I have a 'Title' column with abbreviations (Ceo, Cio,..) which I have to capitalize. What is the most efficient way of finding 3 lettered words and capitalizing them?

Sample data :

Title
-----------------------
Vp - business, Cio, Ceo
E Vp, Cfo
Ceo
Cio
Vp

Thank you so much!

Answer 1

UPDATE roles SET
title = UPPER(title)

I'm confused about the 3-letter requirement. Why would "Vp" not get capitalized? If you really need it:

WHERE LENGTH(title) = 3

EDIT

In response to your comment below:

UPDATE roles SET
title = REPLACE(title, 'Ceo', 'CEO')

You will need to do that query for each 3-letter word.

Answer 2

Making string modifications in the database is generally messy, so I think the best way to handle this is to find all the rows containing the words you are interested in, select them, make the substitution in the client language of your choice, then update the rows back to the database with the replaced string.

For the SQL part you can use a regular expression:

SELECT *
FROM table1
WHERE Title RLIKE '[[:<:]]ceo[[:>:]]'
   OR Title RLIKE '[[:<:]]vp[[:>:]]'
   OR ...

If you just want to find all three letter words as your title implies then use this:

WHERE Title RLIKE '[[:<:]][[:alpha:]]{3}[[:>:]]'