|
|
Posted by Andy Hassall on 11/13/93 11:30
On 27 Oct 2005 10:51:08 -0700, "frizzle" <phpfrizzle@gmail.com> wrote:
>Yes, apparently you're right Malcolm.
It's customary to quote some context.
>Thanks for pointing this out for me. Yet 1 question: if a user enters a
>Nick,
>but decides to remove it later, the field's value isn't actually NULL,
>but "" if
>i'm right.
>What would be the solution for this?
The correct approach would seem to be to actually set it to NULL instead of ''
if the user blanks it out, since it's a "not applicable" bit of data, rather
than the user's nickname actually being an empty string.
If you're not going to do that then you can work around in the query:
mysql> select * from example;
+--------+-----------+
| name | nick |
+--------+-----------+
| John | JohnnyBoy |
| Dean | NULL |
| George | Jaws |
| Chris | Ape |
+--------+-----------+
4 rows in set (0.01 sec)
mysql> update example set nick='' where nick is null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from example;
+--------+-----------+
| name | nick |
+--------+-----------+
| John | JohnnyBoy |
| Dean | |
| George | Jaws |
| Chris | Ape |
+--------+-----------+
4 rows in set (0.00 sec)
mysql> select case
-> when nick is null or nick='' then name
-> else nick
-> end nick_or_name
-> from example
-> order by nick_or_name;
+--------------+
| nick_or_name |
+--------------+
| Ape |
| Dean |
| Jaws |
| JohnnyBoy |
+--------------+
4 rows in set (0.01 sec)
--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Navigation:
[Reply to this message]
|