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.