Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 (
id INT NOT NULL AUTO_INCREMENT,
article_id INT NOT NULL,
summary VARCHAR(255),
body_text TEXT,
PRIMARY KEY(id));

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.

Help?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|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
    
You made me wealthier today, thank you $$$ –  RolandoMySQLDBA May 5 '11 at 18:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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