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.