MySQL - Functions

mysql

How can we use the CAST or CONVERT function?

SELECT CONVERT('abc' USING utf8); //  converts the string 'abc' in the default character set to the corresponding string in the utf8 character set
SELECT CAST(PROD_CODE AS UNSIGNED) FROM PRODUCT; // cast a string column as an unsigned integer
SELECT CAST(PROD_CODE AS SIGNED) FROM PRODUCT; // cast a string column as an unsigned integer
  • CONVERT(… USING …) is standard SQL syntax. The non-USING form of CONVERT() is ODBC syntax.
  • CONVERT() with USING converts data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names.

When you invoke a function, there must be no space between the function name and the opening parenthesis. It is possible to change this default behavior by enabling the IGNORE_SPACE SQL mode to cause spaces after the function name to be ignored.

LEAST() and GREATEST() take a set of values as argument and return the one that is smallest or largest:

SELECT LEAST(4,3,8,-1,5), LEAST('cdef', 'ab', 'ghi');
SELECT GREATEST(4,3,8,-1,5), GREATEST('cdef','ab','ghi');

INTERVAL() takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it:

SELECT INTERVAL(2,1,2,3,4);
WHERE id IN(13,45,97,142);

Using IN() is much more efficient than a list of OR. Arguments to IN() may be of any type, although generally all values given within a list should all have the same type. Elements in a list may be given as expression that are evaluated to produce a value. If the expression references a column name, the column is evaluated for each row. IN() always returns NULL when used to test NULL. That is NULL IN(list) is NULL for any list of values, even if NULL is included in the list.

WHERE id BETWEEN 5 AND 10;

IF() tests the expression in its first argument, and return its second or third argument depending on whether the expression is true or false:

SELECT IF( 1 > 0, 'yes', 'no');

The CASE construct is not a function, but it too provides flow control. It has two form of syntax:

CASE case_expr
    WHEN when_expr THEN result
    [WHEN when_expr THEN result] ...
    [ELSE result]
END

The expression case_expr is evaluated and used to determine which of the following clauses in the rest of the CASE to execute. The when_expr in the initial WHEN clause is evaluated and compared to case_expr. If the two are equal, the expression following the THEN is the result of the CASE. The following CASE expression returns a string that indicates whether the value of @val user variable is 0, 1 or something else:

SET @val = 1
SELECT CASE @val
    WHEN 0 THEN '@val is 0'
    WHEN 1 THEN '@val is 1'
    ELSE '@val is not 0 or 1'
END AS result;

The second syntax for CASE looks like this:

CASE
    WHEN when_expr THEN result
    [WHEN when_expr THEN result] ...
    [ELSE result]
END

Look closely, there is no case_expr. The conditional expression in each WHEN clause is execute until one is found to be true, and then its corresponding THEN expression becomes the result of the CASE.

Note that **IF() and CASE used in expressions have somewhat different syntax than the IF and CASE statements that can be used within compound statements.

ROUND() performs rounding of its argument. The rounding method applied to the fractional part of a number depends on whether the number is an exact or approximate value. A fraction of .5 or greater rounds away from zero, and a fraction less than .5 rounds toward zero. For approximate values, ROUND() uses the rounding method provided in the C library used by the MySQL server. This can vary from system to system, but typically rounds to the nearest even integer.

FLOOR() returns the largest integer not greater than its argument.

CEILING() returns the smallest integer not less than its argument.

ABS() return the absolute value.

SIGN() extract the sign of numeric values.

DEGREES() convert radian (argument) into degrees.

RADIANS() converts degrees (argument) into radian.

LENGTH() determine the string length in bytes.

CHAR_LENGTH() determine the string length in number of characters.

CONCAT() concatenates all of its arguments. CONCAT() returns NULL if any of its arguments are null.

CONCAT_WS() interprets its first argument as a separator to place between the following arguments. CONCAT_WS() ignores NULL values.

STRCMP() functions compares two strings and returns -1, 0, or 1 if the first string is less than, equal to, or greater than the second string.

ENCODE() and DECODE()

DES_ENCRYPT() and DES_DECRYPT() can be used if SSL support is enabled.

AES_ENCRYPT() and AES_DECRYPT() can be considered the most secure of the pairs.

YEAR(), MONTH(), DAYOFMONTH(), DAYOFYEAR(), HOUR(), MINUTE(), SECOND().

MAKEDATE() and MAKETIME() compose dates and times from component values. MAKEDATE() produces a date from year and day of year arguments:

SELECT MAKEDATE(2010,105);

MAKETIME() produces a time from hour, minute, and second arguments:

MAKETIME(9,23,57);

If you need to determine the current date or time, use CURRENT_DATE or CURRENT_TIME. To get current date and time as a single value, use CURRENT_TIMESTAMP or NOW(). The three functions (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP) are unlike most functions in that they can be invoked with or without parentheses.

ISNULL() is true if its argument is NULL, and false otherwise.

IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned, otherwise, the function returns its second argument.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License