I would like to search a body of text to find out when a specific word has been upper cased. MySQL has a REGEXP function with the POSIX character class [[:upper:]], but for the life of me I can't get it to return any results.

Say I want to search a column for the string literal 'FREE' only in upper case.

Let's say the table structure is something like:

CREATE TABLE article_body (
article_id INT NOT NULL,
summary VARCHAR(255),
body_text TEXT,

I want to find body_text columns where the word FREE is in all uppercase, e.g.

SELECT id, summary FROM article_body WHERE body_text REGEXP '[[:upper:]]+FREE';

But of course that syntax is invalid.



From the article on mysql REGEXP:

REGEXP is not case sensitive, except when used with binary strings.

A simple solution would be to use the BINARY operator:

SELECT * FROM article_body WHERE body_text REGEXP BINARY "FREE";

As to why your example doesn't work, the way I interpret it is "Any string with one or more uppercase letter followed by FREE", but of course FREE is case-insensitive there.

| improve this answer | |
  • I ran your query against the sample data from my answer. Your answer is THE SILVER BULLET with the exact precision strike !!! My answer was an oversized atomic bomb. +1 for your answer !!! – RolandoMySQLDBA May 5 '11 at 14:54
  • @Rolando yeah, I learned about the BINARY operator on this site, so I'm just spreading the wealth :D – Derek Downey May 5 '11 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.