Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned surrounded by single quotes and with each instance of single quote (`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word ``NULL'' without surrounding single quotes. The QUOTE() function was added in MySQL 4.0.3.
mysql> SELECT QUOTE('Don\'t!');
-> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
-> NULL
---
Now after reading this I thought that select quote(NULL) would return the string 'NULL' (without quotes) and not the NULL value. However, this is not true on the version of mysql that I'm using:
You've misunderstood, then. Without the quotes, NULL is the NULL value, not a string. You have to have quotes to be a string! NULL is NULL, 'NULL' is a string. MySQL is doing exactly what the manual says.
mysql> select version(); +---------------------+ | version() | +---------------------+ | 4.0.22-standard-log | +---------------------+ 1 row in set (0.00 sec)
mysql> select isnull(quote(NULL)); +---------------------+ | isnull(quote(NULL)) | +---------------------+ | 1 | +---------------------+ 1 row in set (0.01 sec)
mysql> select isnull(quote('not null')); +---------------------------+ | isnull(quote('not null')) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec)
So I guess I'm just confused by the documentation. It's easy enough to get the string 'NULL' by using select ifnull(quote(NULL), 'NULL') or something similiar.
You don't need IFNULL for that.
mysql> SELECT QUOTE('NULL'); +---------------+ | QUOTE('NULL') | +---------------+ | 'NULL' | +---------------+ 1 row in set (0.00 sec)
Based on the functionality I experienced I thought that the documentation should read something like:
---
QUOTE(str)
... If the argument is NULL, the return value is NULL. ...
---
That is what it says, just not in those words. Your wording is better, though, in my opinion.
Does this seem correct? Have I missed or overlooked something?
It is correct in that it behaves as documented.
Cheers. Toro
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]