More SQL

This is a quick addendum to the previous post about using LIKE in a WHERE clause.

LIKE is treated in different ways depending on what database you are using, case sensitivity is a particular area where the differences can be seen and can become important.  MySQL for example, treats like as case-insensitive (you can set it to be case sensitive, but by default it ignores case – see http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html for more information).  But in many other database servers, for example Oracle, LIKE is case-sensitive by default.

While this may seem semi trivial (as I found out today) people often assume searches are case-insensitive and thus having a case sensitive query can be a big problem.

One way to get around this is to use the keyword UPPER to use an all uppercase version of the field to compare with the search string (which you should ensure is also all uppercase.  (LOWER may also be used to compare with all lowercase strings).

It should be noted this is not a great method if you are searching a large database as it will perform a full-text search instead of utilizing indexes, thus increasing the time each query takes.

An example:
SELECT * FROM tablename WHERE UPPER(lastname) LIKE '%SMITH%';
or
DELETE FROM tablename WHERE LOWER(firstname) LIKE '%john%';

Googling UPPER, sql will give you some other methods for case-insensitive searching that utilizes indexes and speeds things up. This is just a base level fix that is useful for smaller databases/tables.

A little SQL

Recently I have been learning how very powerful SQL (structured query language) can actually be.  In this post, I would like to focus on the LIKE keyword which allows you to search a database for a pattern or string.

But first, a small amount of introduction.

SQL is a language used (generally) to ‘query’ databases and edit, delete or retrieve information from these databases.  It can be used with a great majority of existing (structured, relational) databases – anything from MySQL to Microsoft Access to Oracle.  It uses keywords in conjunction with ‘field’ names  (fields are represented (generally) by columns inside of a graphical view) and table names (tables are groups of fields somehow related to each other – for example a ‘person’ table might have a first name, a last name, a date of birth, a height ect.).  With these keywords you can do anything from count the number of entries in a table to find everyone (in the person example) whose last name contains an ‘N’.

A more in depth look at all the SQL keywords, what they do, and how they should be used can be found at http://www.w3schools.com/Sql/default.asp.

Some Common Examples: (this is a very minimal list, check the above link for more)

(* is a wildcard representing ‘all’)

SELECT – retrieves rows containing the specified fields from the given table(s). Used when you are trying to get information out of a database.
This will select all the fields and all the rows (it will retrieve ALL the information in a table) named ‘table’
SELECT * FROM table;

INSERT INTO – allows you to insert values into a new row in a table.
INSERT INTO table (field1, field2, field3) VALUES (value1, value2, value3);

UPDATE – allows you to change the values in (a) row(s) that already exist in a table. (the following statement would set ALL field1’s = value1 and ALL field2’s = value2)
UPDATE table SET field1='value1', field2='value2';

WHERE – begins a ‘WHERE clause’ which specifies which rows you want to effect with your query.
SELECT * FROM table WHERE field1='value1';
UPDATE table SET field1='value1', field2='value2' WHERE field3='value3';

Now, the WHERE clause is the place that it gets interesting (at least in terms of using LIKE).

When creating a ‘WHERE clause’ LIKE can be used to search for entries that match a pattern instead of looking for a specific value. This can be done in the following ways:

Using % as a wildcard
WHERE field1 LIKE '%s' – look for entries that have a field1 that ENDS in s – for example: would find ‘words’, ‘values’… would NOT find ’soon’.
WHERE field1 LIKE 's%' – would find entries with field1 starting with s (like ’soon’)
WHERE field1 LIKE '%s%' – would find entries that have a field1 which contains s anywhere in it

Using _ to represent an unknown character
WHERE field1 LIKE 's_op' – would find things like slop and stop

Anyway, LIKE can be an extremely useful and effective way to implement a search. An example would be an users admin page which allowed the admin to search for the name/email of the person they wanted to edit. They may not know the persons full name, or they may not want to/know to type it exactly as it is in the database. Adding a LIKE clause to your WHERE clause is an easy and efficient way to provide this capability.