The FIELD() function is, in a sense, the inverse of ELT() in that ELT will return a string from a specific numbered position in an array of strings, while FIELD() returns the field position that matches (not the same as “contains”) the string listed as the first parameter. Syntax:

SELECT FIELD(‘string_to_match’, ‘string1′, ‘string2′, ‘etc.’)

Here is the field function in action:

SELECT FIELD(‘Scarlet’, ‘Melanie’, ‘Rhett’, ‘Scarlet’, ‘Ashley’, ‘Scarletta’)

This will return “3″. Though 5 contains “Scarlet”, it is not equivalent to “Scarlet”. Had position 5 also been equivalent to “Scarlet” the result would still have been only “3″. Later matches are ignored.

Both FIELD() and ELT() are a kind of test. They can be used when you are looking for one item out of a group of alternatives. The result can be cranked through a series of if/then statements or switch/case to determine a course of action. This is another place where some of the work done by the server can be taken on by the database.

Read more about SQL Functions.

In the course of writing my book on SQL, I had the opportunity to explore a function I had not heard of before, the EXPORT_SET() function. In my investigations about this function, I discovered that I am not the only person dubious about it. The thing is, every explanation I could find for it was nearly word for word the same down to the examples. This was a good indication that the authors were just parroting each other. When I write about a function I try to use it. At least I will go to the mySQL interface and try out a few variations on it. In this way I get a more intimate knowledge than is possible just reading something someone else has written, especially when that something else is incomplete.

My problem with the EXPORT_SET() function is that I was having trouble figuring out what I could do with it. So I investigated this aspect for an hour or two, surfing here and there I found nothing that would explain why you would ever want to use a function that basically returns an array of bits that comprise a number and lists them in reverse order. Obviously someone wrote this function for a vital purpose. I thought the purpose mignt be found in the name. EXPORT is a clue. After thorough investigation I see it has been used to port data between applications. A few thought experiments made me realize it could also be used in security as a means of encrypting data. I am sure there is more to it.

SQL is broken up into several sub-languages. One of these is DCL which stands for Data Control Language. What it is really about is designating who has permission to do what on a database. Ultimately, this is a security thing. If we could implicitly trust everyone, we would just grant all power to everyone. In the real world of the anonymous internet this would fall into the category of FAIL.

Thus, SQL gives us the ability to GRANT and REVOKE. Actually, on php myadmin in cpannel, you cannot do this with an SQL command, it is done through a UI when the user is created. Basically GRANT gives permission to do various things, and REVOKE takes those permissions away.

The system works logically in that a user has no permissions unless first granted them. Thus we have no REVOKEs without first, somewhere along the line, having a GRANT. The key to GRANTs is to keep them to a minimum. The less access users have to a database the less likely they are to screw things up. Of course, this must be balanced with the needs of the many, which Spock will tell you, out-weighs the needs of the few, we happy few, we band of brothers. Never thought you would see Spock and Henry V quoted in the same sentence did you? Suffice it to say there is a balance.

For most purposes, REVOKEs are used when an empolyee leaves a company or a user loses status (say a subscription lapses).  The syntax for REVOKE follows:

REVOKE privilege
ON object
FROM user or PUBLIC or role

For a GRANT, just replace “REVOKE”.

Okay, let’s get our act together. To find out if a cell is NULL why not just ask…ISNULL? Well, yes, this does work for mySQL. But it isn’t that simple. What if we wanted an easy way to replace the null value with something else? Good idea you say. So the powers that be at mySQL came up with IFNULL(). It looks like this:

SELECT IFNULL(column1, “alternative”) FROM table

So if the expression in column1 turns out to be NULL you can make it something else.

Where it gets a bit confusing is that some applications use ISNULL() the way mySQL uses IFNULL(). And some use a completely different syntax. NVL() is how it looks in Oracle.

But there is another way of doing this available in most applications. It is called COALESCE(). This looks alot like IFNULL (mySQL) but has the added feature that it can take multiple parameters and will in the end return the first one that it finds that is NOT NULL. So you can do something like this:

SELECT COALESCE(column1, column2, etc., “alternative”) FROM table

This will return the first not null param that it runs across. Leave the failsafe for the end.