Case Sensitive SQL Search

MySQL on default installation uses the case insensitive configuration meaning the data you stored and searched will not be case sensitive.

Let us first check our current server and database’s appropariate variables.

SHOW VARIABLES
WHERE variable_name = 'character_set_server'

> latin1

SHOW VARIABLES
WHERE variable_name = 'collation_database'

> latin1_swedish_ci

Since we are using latin based language settings, the cases are different unlike some languages that don’t have cases. And with the database collation set to latin_swedish_ci the string search will be case insensitive (ci).

For this illustration, I have created a table “country” with single column “country_name” and inserted 3 rows.
CREATE TABLE country (
country_name varchar(32) NOT NULL
);
INSERT INTO country
VALUES ('US'), ('UK'), ('ukraine')
;
COMMIT;
With the following SELECT I can look up the table and get all country names that begin with u or U.

SELECT country_name FROM country
WHERE country_name like 'u%';
I need to correct ‘ukraine’ to ‘Ukraine’ and can do so by the following statement.

UPDATE country
SET country_name = concat('U', substring_index(country_name, 'u', -1))
WHERE BINARY country_name like 'u%';
COMMIT;

NOTE the use of ‘BINARY’ in the where clause above.

This is a simple example which effectively demonstrates the use of case sensitive matching.
Enjoy,

Shiva



Google Docs Spreadsheet – Better access control needed

I use Google docs to maintain many documentations for a non-profit organization and this has been a very useful, money & time saving application. In my view, following three features will immediately enhance what is already a excellent collaborative product.  These are low-hanging fruits.

  • User access control at worksheet level to view
  • Copying more than 1000 (one thousand cells) at a time
  • Multi-column sorting

In our non-profit organization we have multiple chapters controlling their own documents/ spreadsheets and some times there are situations where I would like to have a single spreadsheet but each work sheet is visible only to a particular chapter or member. For example, a common spreadsheet with 50 worksheets (one for each US state) should appropriately be visible to these chapters.  A California chapter can view California worksheet only and not others.

Currently, I can control the editing access through “Protect Sheet” but not the viewing. See below.

Google, could you please extend the feature to control the viewing along with editing?

Another feature that would be very useful would be copy more than 1000 cells at a time from one spreadsheet to another.   If a worksheet has 20 columns and 250 rows and if one likes to duplicate this data with copy and paste (^C & ^V), she would need 5 attempts :(.

Google, could you remove this limitation?

And finally, sorting by more than one column.  Currently one can sort by a selected (single) column only. Though, this is not a major hindrance but by providing multi-column sorting feature I can avoid bringing data to Excel when I need to sort the data in multiple ways.

Thanks,