MySQL 3.23, 4.0, 4.1 Reference Manual :: 11 Functions and Operators :: 11.10 Cast Functions and Operators

« 11.9.6 Fine-Tuning MySQL Full-Text Search

Section Navigation [Toggle]

11.10. Cast Functions and Operators

Table 11.14. Cast Functions

Name	Description
`BINARY`	Cast a string to a binary string
`CAST()`	Cast a value as a certain type
`CONVERT()`	Cast a value as a certain type

BINARY
The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be done byte by byte rather than character by character. This causes the comparison to be case sensitive even if the column is not defined as BINARY or BLOB. BINARY also causes trailing spaces to be significant.
```
mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0
```
In a comparison, BINARY affects the entire operation; it can be given before either operand with the same result.
BINARY was added in MySQL 3.23.0. As of MySQL 4.0.2, BINARY str is a shorthand for CAST(str AS BINARY).
Note that in some contexts, if you cast an indexed column to BINARY, MySQL is not able to use the index efficiently.
CAST(expr AS type)
The CAST() function takes a value of one type and produce a value of another type, similar to CONVERT(). See the description of CONVERT() for more information.
CONVERT(expr,type), CONVERT(expr USING transcoding_name)
The CONVERT() and CAST() functions take a value of one type and produce a value of another type.
The type can be one of the following values:
- BINARY (and BINARY[N] as of MySQL 4.1.1)
- CHAR (and CHAR[N] as of MySQL 4.1.1)
- DATE
- DATETIME
- SIGNED [INTEGER]
- TIME
- UNSIGNED [INTEGER]
BINARY produces a string with the BINARY data type. See Section 10.4.2, “The BINARY and VARBINARY Types” for a description of how this affects comparisons. If the optional length N is given, BINARY(N) causes the cast to use no more than N bytes of the argument. Similarly, CHAR[N] causes the cast to use no more than N characters of the argument.
CAST() and CONVERT() are available as of MySQL 4.0.2. The CHAR conversion type is available as of 4.0.6. The USING form of CONVERT() is available as of 4.1.0.
CAST() and CONVERT(... USING ...) are standard SQL syntax. The non-USING form of CONVERT() is ODBC syntax.
CONVERT() with USING is used to convert data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the default character set to the corresponding string in the utf8 character set:
```
SELECT CONVERT('abc' USING utf8);
```

If you want to compare a BLOB value or other binary string in case-insensitive fashion, you can do so as follows:

Before MySQL 4.1.1, use the UPPER() function to convert the binary string to uppercase before performing the comparison:
```
SELECT 'A' LIKE UPPER(blob_col) FROM tbl_name;
```
If the comparison value is lowercase, convert the string value using LOWER() instead.
For MySQL 4.1.1 and up, binary strings have no character set, and thus no concept of lettercase. To perform a case-insensitive comparison, use the CONVERT() function to convert the value to a nonbinary string. Comparisons of the result use the string collation. For example, if the character set of the result has a case-insensitive collation, a LIKE operation is not case sensitive:
```
SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tbl_name;
```
To use a different character set, substitute its name for latin1 in the preceding statement. To specify a particular collation for the converted string, use a COLLATE clause following the CONVERT() call, as described in Section 9.1.8.2, “CONVERT() and CAST()”. For example, to use latin1_german1_ci:
```
SELECT 'A' LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci
  FROM tbl_name;
```

CONVERT() can be used more generally for comparing strings that are represented in different character sets.

LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a nonbinary string:

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York    | new york                          |
+-------------+-----------------------------------+

The cast functions are useful when you want to create a column with a specific type in a CREATE ... SELECT statement:

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

The functions also can be useful for sorting ENUM columns in lexical order. Normally, sorting of ENUM columns occurs using the internal numeric values. Casting the values to CHAR results in a lexical sort:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(str AS BINARY) is the same thing as BINARY str. CAST(expr AS CHAR) treats the expression as a string with the default character set.

Note

In MySQL 4.0, a CAST() to DATE, DATETIME, or TIME only marks the column to be a specific type but does not change the value of the column.

As of MySQL 4.1.0, the value is converted to the correct column type when it is sent to the user (this is a feature of how the new protocol in 4.1 sends date information to the client):

mysql> SELECT CAST(NOW() AS DATE);
       -> 2003-05-26

As of MySQL 4.1.1, CAST() also changes the result if you use it as part of a more complex expression such as CONCAT('Date: ',CAST(NOW() AS DATE)).

You should not use CAST() to extract data in different formats but instead use string functions like LEFT() or EXTRACT(). See Section 11.7, “Date and Time Functions”.

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number:

mysql> SELECT 1+'1';
       -> 2

If you use a string in an arithmetic operation, it is converted to a floating-point number during expression evaluation.

If you use a number in string context, the number automatically is converted to a string:

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

For information about implicit conversion of numbers to strings, see Section 11.2, “Type Conversion in Expression Evaluation”.

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned by default (see Section 11.6.1, “Arithmetic Operators”). You can override this by using the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT CAST(1-2 AS UNSIGNED)
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
        -> -1

If either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context, DECIMAL column values are regarded as floating-point values.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

The handing of unsigned values was changed in MySQL 4.0 to be able to support BIGINT values properly. If you have some code that you want to run in both MySQL 4.0 and 3.23, you probably cannot use the CAST() function. You can use the following technique to get a signed result when subtracting two unsigned integer columns ucol1 and ucol2:

mysql> SELECT (ucol1+0.0)-(ucol2+0.0) FROM ...;

The idea is that the columns are converted to floating-point values before the subtraction occurs.

If you have a problem with UNSIGNED columns in old MySQL applications when porting them to MySQL 4.0, you can use the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. However, as long as you use this option, you are not able to make efficient use of the BIGINT UNSIGNED data type.

Previous / Next / Up / Table of Contents

User Comments

Posted by Are you mortal? Then prepare to die. on November 3 2004 11:31am

[Delete] [Edit]

Be careful that this 'feature' of MySQL dosn't bit you in the ass.

For example, imagine I have a table with two 'unsigned' integer columns (still with me?). Lets call those columns 'one' and 'two'. Now imagine the following query...

# Query to select the 10 biggest differences between two colums
SELECT one - two AS diff
ORDER BY one - two
LIMIT 10;

If the result of 'one - two' is negative (imagine that the value of two is bigger than one), then you end up with values of 18446744073709551615.

This isn't a bug mind you, as this fact is documented this is the expected behaviour!

So, still looking for the 10 biggest differences you might try...

# Query 2 to select the 10 biggest differences between two colums
SELECT one - two AS diff
WHERE one - two < 10000 -- Hopefuly bigger than our biggest differences
ORDER BY one - two
LIMIT 10;

You find it dosn't work, (you still get the 18446744073709551615s) because the internals of subtracting unsigned columns are so messed up.

You need to do the following...

# Query 3 to select the 10 biggest differences between two colums
SELECT CAST(one - two AS SIGNED) AS diff
ORDER BY diff
LIMIT 10;

I hope this example helps

Posted by anne blankert on May 29 2006 10:14pm

[Delete] [Edit]

To convert to numeric, the convert() and cast() functions are less forgiving then the implicit conversion when it comes to the data to be converted. If you want to convert "1a" or "1 apple", "2 apples", " 3 things" to 1, 1, 2 and 3 respectivly, the cast and convert function will produce an error. Instead use select 0+'1a', 0+'1 apple', 0+'2 apples', 0+' 3 things'.

Posted by Ronald Rudy on December 5 2006 5:07pm

[Delete] [Edit]

Here's a workaround for not being able to cast/convert a value during table creation with just a create:

CREATE TEMPORARY TABLE tmp
SELECT 1000000.001-1000000.001 as n;

This will yield a table with the following structure:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| n     | double(20,3) |      |     | 0.000   |       |
+-------+--------------+------+-----+---------+-------+

Posted by Mohamed Infiyaz Zaffer Khalid on January 4 2008 10:07am

[Delete] [Edit]

The cast() function is amazingly useful when working with dates - not to mention date+time.

First, take a look at the following basic example:

select cast('2007-12-25' as DATETIME)

This naturally returns the output:
2007-12-25 00:00:00

But there are REALLY useful practical situations where we'd HAVE to use this function. Here's a case:

I want to pick all records from a table "Sales", where the "TransactionDate" field is between 25-Dec-2007 and 25-Jan-2008. Bear in mind that the field is of type DateTime.

Here's the BASIC query that I'd put in a string if I were to do it in PHP:

"SELECT * FROM Sales WHERE TransactionDate BETWEEN '$D1' and $D2"

In the above case, I'm assuming that $D1 and $D2 are posted variables in PHP. Unfortunately The above line won't work. And THIS is where we can use the CAST() function.

So here's the CORRECT STATEMENT:

"SELECT * FROM Sales WHERE TransactionDate BETWEEN CAST('$D1' as DATETIME) and CAST($D2 as DATETIME)"

This does the job without a flaw!
Happy programming,
Khalid

Posted by Ice Fire on August 26 2008 4:02pm

[Delete] [Edit]

Alphanumeric strings automatically evaluate to 0 when used in a SELECT query to match against a column of type INT.

For example:

Table T1
n: INT
n_squared: INT,

n n_squared
0 0
1 1
2 4
3 9
4 16
...

Q1: SELECT * FROM T1 WHERE n_squared='ABC';
Q2: SELECT * FROM T1 WHERE n_squared='ABC123';

Both queries produce identical results:

n n_squared
0 0

Posted by D'n Russler on June 3 2009 11:09am

[Delete] [Edit]

To save others time in searching for something that wasn't immediately obvious to me...

A list of character sets is in 9.1.12. Character Sets and Collations That MySQL Supports, http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

Posted by Jon Lewis on February 24 2010 5:42pm

[Delete] [Edit]

Here's another workaround for the lack of data types: create your own conversion function. I am working with a DB (unfortunately) converted from and used by MS Access. All the primary keys are therefor of type INT(10) SIGNED. When UNIONing a constant integer (SELECT 0 AS Key) with a queried value the result is of the wrong type. So, since "CAST(0 AS INT(10))" is not an option I created my own:

CREATE FUNCTION `IntToInt10`(TheInt INT)
RETURNS INT(10) SIGNED -- here is the trick
DETERMINISTIC
BEGIN
RETURN TheInt;
END

Now, whenever I need to force a result to be of type int(10) I just run it through this function. I'm sure it would work for other types too (although a bit more involved if converting between two different classes of data such as numeric to alpha). Hope that helps.

Posted by guillaume - on June 23 2010 3:14pm

[Delete] [Edit]

If you need to convert an id to a part of a string, the easiest way is to:

WHERE dst LIKE CONCAT("%", CAST(fs.nid as BINARY),"%")

Posted by Darren Cassar on September 7 2010 8:15pm

[Delete] [Edit]

Note that if you need to set collation for a cast you will have to do it after you finish the cast expression as explained: http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

Posted by ZAky ke on March 17 2011 10:27am

[Delete] [Edit]

How do I cast column value not a string?

SELECT CONVERT(_latin1city USING utf8) from users;

Where city is a column in users table.

I need to this statement for insert statement for another table.

Posted by Chris Wagner on September 28 2011 10:47pm

[Delete] [Edit]

One of the glaring omissions in MySQL is the inability to convert between binary strings and integers and IP's. Here is a way to do it.

Binary string to integer:
conv(hex(binfield), 16, 10)

Binary string to IP:
inet_ntoa(conv(hex(binfield), 16, 10))

Posted by Martin Lester on November 15 2011 3:07pm

[Delete] [Edit]

Convert HEX (string) to DECIMAL

SELECT CAST(X'01ABCDEF' AS DECIMAL);

Thought that would be in the basic examples.

Posted by Eric Kent on December 23 2011 5:15pm

[Delete] [Edit]

The comment by anne blankert on May 29 2006 is not correct, at least with the current version. In a script, both of these will return the expect value of 1:
select cast(‘1a’ as unsigned);
select 0 + ‘1a’;
However, both generate a warning (1292 Truncated incorrect … value).
When casting a character value to a numeric, the character value must be a properly formed number representation. In a script you can simply ignore the warning, the script will return the expected value and continue. However, in a stored procedure, even if you handle the warning with a condition handler, the statement:
set iValue = 0 + ‘1a’;
will return null.
To extract the numeric part of a string, you need to create a function that will parse it using substr().

Add your own comment.

Top / Previous / Next / Up / Table of Contents