CODEWITHSUNDEEP
mysqlstring
ThinkCode
ThinkCode

Reputation: 7961

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

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!

Upvotes: 1

Views: 246

Answers (2)

Mark Byers
Mark Byers

Reputation: 838796

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}[[:>:]]'

Upvotes: 1

Amy B
Amy B

Reputation: 17977

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.

Upvotes: 1

Related Questions