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.