5 Useful MySQL Functions And Control Flows

Wildhoney · 09-06-2007, 03:20 PM

1: IF()

If statements have been a life saver on many occasions. They are invaluable for checking a value then returning another value different from the one stored in the table.

sql Code:

SELECT
    IF(myColumn1 = 1, 'Yes', 'No') AS myIfColumn
FROM
    myTable

Result: If myColumn1 equals 1 then Yes else No.

2: ELT()

Similar to an if else statement. The ELT takes the first value and returns the relative offset from the other arguments.

sql Code:

SELECT
    ELT(myColumn1, 'One', 'Two', 'Three', 'Four') AS myELTColumn
FROM
    myTable

Result: If myColumn1 equals 3 then Three.

3: SOUNDEX()

Not as impressive as you would think from your initial perceptions. However, SOUNDEX is good in some instances. The first letter must match though else SOUNDEX will fail.

sql Code:

SELECT
    myColumn1,
    myColumn2
FROM
    myTable
HAVING
    SOUNDEX(myColumn1) = SOUNDEX('Wyldhoney') AS mySoundexColumn

Result: If myColumn1 equals Wildhoney then it would be returned because it sounds like Wyldhoney.

4: DATE_FORMAT()

Much confusion has arisen amongst beginners and intermediates to MySQL about how to format the date properly. The DATE_FORMAT function proves precious to output the date in a format you desire. No matter what format the date is stored in, MySQL date or as a UNIX timestamp, DATE_FORMAT will format the date with no questions asked.

sql Code:

SELECT
    DATE_FORMAT(myColumn1, '%D %b, %Y') AS myDate
FROM
    myTable

Note: The other way to format the date is at the programming level, such as with strtotime.

Result: For example, 4th Aug, 2007.

5: REGEXP

Last but not least, REGEXP can be used in MySQL in place of the more primitive LIKE. It uses the standard PCRE syntax.

sql Code:

SELECT
    myColumn1,
    myColumn2
FROM
    myTable
WHERE
    myColumn1
REGEXP
    '^W[A-Z]+y$'

Result: This will return any strings that begin with W and end in Y. Wildhoney, for instance, will be brought back, but TalkPHP would not. MySQL's REGEXP is case-insensitive and you may also use NOT REGEXP to reverse the outcome. In the above example using NOT REGEXP would return everyone but members that do not begin with W and end in Y.