As noted earlier, ABS() returns the absolute value of its argument. The SIGN() function (known in mathematics as the signum function and often written sgn(x) ) tests the sign of its argument, and returns 1 if the argument is positive, Ė1 if itís negative, and 0 if the argument is 0.
To obtain the square root of a number, use the SQRT() function, as shown here:
Itís often useful or necessary in programming to generate random numbers. MySQL provides this capability using the RAND() function, which returns a pseudo-random floating-point value from 0 to 1. An integer may be passed to this function as an optional argument; this serves to ďseedĒ the function and allows you to produce a fairly random but repeatable sequence of values. You can also use RAND() to obtain a set of values in random order from a query by including it in an ORDER BY clause.
In nearly any programming application, you encounter situations in which itís necessary to convert data from one type to another. For example, you may have a variable whose value is the string "535" , and you may need to be able to treat this like the number 535 in order to multiply it by another value. Much the same is true in MySQL. In many cases, MySQL handles the conversion for you. For example, if you try to multiply the number 5 by the string "5", the latter is automatically converted to a number, so that the result is the number 25. However, there are times when MySQLís built-in type conversions donít do what you want them to, and you need to force the issue.
MySQL has two types of conversion functions for this purpose: the first is for casting datatypes to other datatypes (and in MySQL 4.01 and newer, for converting strings in one character set to another), and the second is for converting numbers in one base to another.
While MySQL generally does a pretty good job of converting between datatypes, there may be times when itís necessary to perform such conversions explicitly. Beginning in MySQL 4.0.2, two functions are provided for this purpose. Both CAST() and CONVERT() take an argument of one type and attempt to return the same value converted to a different datatype. As of MySQL 4.1, CONVERT() can also be used to convert strings from one character set to another.
The CAST() function has the following syntax:
CAST(expression AS type)
where expression is a value or an expression that evaluates to a value, and type is one of the following datatypes:
When used to convert between datatypes, the CONVERT() function has this syntax:
This function can use any of the same values for type as CAST() does.
These functions are most useful when creating a new table using CREATE ... SELECT ... or copying data between tables using an INSERT ... SELECT ... query. For instance, suppose that you have a TEXT column in a table that you would like to have sorted using case-sensitivity. Instead of using BINARY textcol in each of your queries, you can copy the table definition and use this statement:
CREATE newtable SELECT CAST(textcol AS BINARY) FROM oldtable;
or this one:
INSERT INTO newtable SELECT CAST(textcol AS BINARY) FROM oldtable;
In the latter case, youíll need to make sure that the column into which youíre selecting was declared as BINARY. (If you copied the definition of the old table, youíll need to change the column type using ALTER TABLE.)
The second form of CONVERT() has the following syntax:
CONVERT(expression USING encoding)
where encoding is the name of a character set, such as in this example:
SELECT CONVERT('I like MySQL' USING utf8);
You can see which character sets are available on your installation by using SHOW CHARACTER SET; .
blog comments powered by Disqus