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.
SELECT IF(myColumn1 = 1, 'Yes', 'No')AS myIfColumn FROM myTable
Result: If myColumn1 equals 1 then Yes else No.
Similar to an if else statement. The ELT takes the first value and returns the relative offset from the other arguments.
SELECT ELT(myColumn1, 'One', 'Two', 'Three', 'Four')AS myELTColumn FROM myTable
Result: If myColumn1 equals 3 then Three.
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.
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.
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.
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.
Last but not least, REGEXP can be used in MySQL in place of the more primitive LIKE. It uses the standard PCRE syntax.
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.
The man who comes back through the Door in the Wall will never be quite the same as the man who went out.
Last edited by Wildhoney : 12-09-2007 at 04:36 PM.
The Following 6 Users Say Thank You to Wildhoney For This Useful Post:
It is fast, but you can expect similar performance to PHP's preg functions. Thus, basically it would work on a small data set but not on a really large one, from what i noticed, it had a similar performance to "LIKE" which most of you will know isnt very good at all...
MySQL uses Henry Spencer's implementation of regular expressions, which is aimed at conformance with POSIX 1003.2.
Just for the record, that's another regex flavor than PCRE. I believe it's the same as the PHP ereg functions.
Word boundary metacharacters, for example, are not \b but [[:<:]] and [[:>:]].
Also I believe POSIX keeps on searching for the longest match, whereas PCRE returns the first found match. Sounds a bit vague probably, anyway, my point is that MySQL's REGEXP and PCRE are not the same.