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!
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}[[:>:]]'
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.
精彩评论