StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You may want to use a case sensitive <a href="http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html" rel="nofollow noreferrer">collation</a>. I believe the default is case insensitive. Example:</p> <pre><code>CREATE TABLE my_table ( id int, name varchar(50) ) CHARACTER SET latin1 COLLATE latin1_general_cs; INSERT INTO my_table VALUES (1, 'SomeThing'); INSERT INTO my_table VALUES (2, 'something'); INSERT INTO my_table VALUES (3, 'SOMETHING'); INSERT INTO my_table VALUES (4, 'SOME4THING'); </code></pre> <p>Then:</p> <pre><code>SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$'; +------+-----------+ | id | name | +------+-----------+ | 3 | SOMETHING | +------+-----------+ 1 row in set (0.00 sec) </code></pre> <p>If you don't want to use a case sensitive collation for the whole table, you can also use the <a href="http://dev.mysql.com/doc/refman/5.1/en/charset-collate.html" rel="nofollow noreferrer"><code>COLLATE</code></a> clause as <a href="https://stackoverflow.com/users/22371/kchau">@kchau suggested in the other answer</a>.</p> <p>Let's try with a table using a case insensitive collation:</p> <pre><code>CREATE TABLE my_table ( id int, name varchar(50) ) CHARACTER SET latin1 COLLATE latin1_general_ci; INSERT INTO my_table VALUES (1, 'SomeThing'); INSERT INTO my_table VALUES (2, 'something'); INSERT INTO my_table VALUES (3, 'SOMETHING'); INSERT INTO my_table VALUES (4, 'SOME4THING'); </code></pre> <p>This won't work very well:</p> <pre><code>SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$'; +------+-----------+ | id | name | +------+-----------+ | 1 | SomeThing | | 2 | something | | 3 | SOMETHING | +------+-----------+ 3 rows in set (0.00 sec) </code></pre> <p>But we can use the <code>COLLATE</code> clause to collate the name field to a case sensitive collation:</p> <pre><code>SELECT * FROM my_table WHERE (name COLLATE latin1_general_cs) REGEXP '^[A-Z]+$'; +------+-----------+ | id | name | +------+-----------+ | 3 | SOMETHING | +------+-----------+ 1 row in set (0.00 sec) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload