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.

Karl · 09-06-2007, 03:52 PM

Some good functions there, thanks. I didn't know we could do REGEX with MySQL, do you know how it is on performance?

Haris · 09-06-2007, 04:11 PM

Oh, there's a if function for mysql.

Thanks Adam.

Wildhoney · 09-06-2007, 05:33 PM

I have absolutely no idea about the speed of MySQL's REGEXP function. If anybody has any idea then I would very much appreciate the information.

localhost · 09-06-2007, 08:10 PM

I'm not sure either, However I may test it out in the near future (tomorrow night), Thankyou for the article though.

bluesaga · 09-06-2007, 10:22 PM

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...

Sand_Devil · 11-30-2007, 10:54 PM

I've always found COALESCE to be very useful.
6: COALESCE

Returns the first non null entry

Code:

SELECT COALESCE (column1, column2, '') from table

Result: if column1 not null, then returns column1, if column1 is null and column2 is not, then will return column2, if both column1 and column2 is null, will return an empty string.

Extremely useful when you need to check for a null statement or need to set a flag if a value is null.

Tanax · 11-30-2007, 11:04 PM

I found everything written in this thread useful :D

Anyhow, you should also write something about this:

Code:

DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Because my experience with it is very nice. For example, when you're using a usersystem, and want to have automatic logouts for everyone who hasn't done anything for 15 minutes.

php Code:

UPDATE
        `$this->db->table['users']`
SET
        `$this->db->col['user_session']` = ''
WHERE
        DATE_SUB(NOW(), INTERVAL 15 MINUTE) > `$this->db->col['user_last_action']`;

Wildhoney · 11-30-2007, 11:04 PM

I never knew about COALESCE. The amount of times I've used conditional statements to check if columns are NULL is unbelievable. Is that a recent addition? As it doesn't even ring a bell.

bluesaga · 12-01-2007, 12:18 AM

One thing i should of mentioned before:

Soundex does not handle non-ascii characters. IE, it will not work for Chinese, Japanese Korean etc :(

Wildhoney · 12-01-2007, 02:53 AM

So it's not only me that thinks the Asian languages all sound like random noise? It's comforting to know.

Sand_Devil · 12-01-2007, 11:16 PM

Quote:

Originally Posted by Wildhoney

I never knew about COALESCE. The amount of times I've used conditional statements to check if columns are NULL is unbelievable. Is that a recent addition? As it doesn't even ring a bell.

I have only been coding in mysql and php for the last year and a half. It was one of the first functions that I discovered (out of necessity).

Geert · 12-09-2007, 04:42 PM

Quote:

Originally Posted by Wildhoney

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.

Quote from http://dev.mysql.com/doc/refman/5.0/en/regexp.html:

Quote:

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.

Wildhoney · 12-09-2007, 05:20 PM

Interesting. I didn't think other flavours of regex were really utilised these days as PCRE is rather prevalent - and quite possibly the most preferred flavour.

bdm · 12-12-2007, 01:32 PM

I remember learning about most of those functions about 8~ months ago in school, but totally forgot about them.

Thank you.

webtuto · 01-01-2008, 03:01 PM

thanks they are very useful i will try them

Village Idiot · 01-02-2008, 08:44 PM

Using #1 on a project right now.