I find it interesting how seldom-used functions are often poorly documented. In the course of my research on SQL I have run across several of these types of functions. In this case we have the FROM_DAYS() function. This is what I wrote for the desk reference:

The FROM_DAYS() function can only be used for dates after the year 1582 (in most of Europe, but not England or Russia) because it was this year the Gregorian Calendar came into effect. The date adjustment made on that date is not taken into account in this function. This is meant to take the number of days since the date represented by 0000-00-00, or the first day anno domini. What is strange about it is that input less than 365 returns simply “0000-00-00”, but numbers of days after that will act properly. Example:

SELECT FROM_DAYS(367)

will result in the date “0001-00-02”.

Not sure why the function does not work for numbers less than 365. I tested the function for negative numbers as well, and there was no go there either. I suppose the creator of this function assumed that people would only be using it for dates after the Gregorian calendar was put into place. He is probably correct in this regard. Still, I was surprised to see it. Also, many examples on the web, especially the one on the oracle site for mySQL actually have incorrect output in their example when compared with the output I found in testing. Their results were exactly seven years off. Probably a typo.

It is easy to get the current date time in mySQL. The simple function is NOW(). This function can be used inside of many other functions including the DAYNAME function which returns the day of the week.

The DAYNAME() function returns the name of the day of the week for a particular date. Example:

SELECT DAYNAME(’2012-05-15′)

will yield “Tuesday”. The function requires a parameter. Simply calling the function will not return the current day. To get the day today in mySQL the following will work:

SELECT DAYNAME(NOW())

Okay, you are given the MAX () function which finds the largest item in a column. You get a hold of the MIN() function which finds the lowest number in a column. Now, you are handed the MID() function as an SQL programmer. What does the MID() function do? Trick question, I know. You are right it does NOT find the middle number in a column.

The MID() function in SQL is used to extract a substring from a larger string. Syntax:

MID(column_or_string, beginning_position, length)

The column or string parameter is the string to be looked at. The beginning position is where to start. Each character position will have a number beginning with 1 (other languages in similar functions will often begin at zero). The length is optional; it tells how many characters, including the first one, are to be included in the substring to be extracted. The length parameter is optional. If left off, the beginning position through the remainder of the string will be the result. This function would be a good way to, for example, come up with the last four digits of a credit card number. For example:

SELECT MID(’1234567890654321′, 13, 4)

Will render: “4321″.

What a handy SQL function.

Often in SQL examples you will see quote marks around a string that is being operated upon. Generally, a straight string is not often used in general practice. It is more likely to be a variable and more likely yet to be a column. Having or not having quote marks can make a huge difference in the results given by a function. We will use the LEFT() function as an example. The left function is a way to get the leftmost characters from a
string.

SELECT LEFT(‘string’, number_of_characters)

To get the 7 left-most characters from a specific column in every row of a table the following would work:

SELECT LEFT(name, 7) FROM fict_chars

It should be noted for this and for other functions that if the string is in single quotes that SQL will see it as a literal string. If it is not, SQL will see it as a column. Thus, had we used ‘name’, the result would have been “name” for each row, and not the actual name drawn from the specified column.

Just because they look alike...

Generally I am a fan of SQL and how it works, but there is a function that I find irritating. Not because of what it does, but because of its name, “INSERT()”. Why, oh why would you give a function the same name as a statement that already exists? I am sure there is a story behind this and it probably goes back to family squabbles occuring in the 10th century AD, or at least to the 1970s when Chamberlin and Boyce first conceived RDBMS. Suffice it to say that it could cause confusion when quickly glancing over code or when newcomers are learning the language.

Just know that the two are different. The statement is the familiar, much used instruction to the database to add a row to a table.

INSERT INTO table (column1, column2, etc.)
VALUES(‘value1′, ‘value2′, ‘etc.’)

Meanwhile, the INSERT() function is designed to replace text within a string. It does this using the following syntax:

INSERT(‘string’, position, length, ‘inserted_string’)

The first parameter is the string to be operated upon. The position is where the insertion should begin. Length defines how many characters should be replaced. The fourth parameter is the string to be injected.

A simple example:

SELECT INSERT(‘Jane Porter was the heroine in Harry Potter.’, 32, 12, ‘Tarzan’)

Our result, “Jane Porter was the heroine in Tarzan.” Note that the character specified in the second parameter is over-written as are the subsequent 11 characters, 12 characters in all.

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.