create a table using the below sql statement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE IF NOT EXISTS `students` ( `id` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `first_name` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, `last_name` varchar(250) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(250) COLLATE utf8_unicode_ci NOT NULL, `age` int(10) NOT NULL, `totalmarks` varchar(250) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `students` -- INSERT INTO `students` (`id`, `created_at`, `updated_at`, `first_name`, `last_name`, `email`, `age`, `totalmarks`) VALUES (23, '2015-04-28 06:09:38', '2015-05-15 23:55:29', 'John', 'mathew', 'vasanthanpv@gmail.com', 33, '333'), (26, '2015-05-16 01:19:24', '2015-05-16 01:19:24', 'vasanthan', 'pv', 'vasanthanpv@gmail.com', 44, '333'), (27, '2015-05-16 01:19:46', '2015-05-16 01:19:46', 'Martin', 'jacob', 'vasanthanpv@gmail.com', 22, '22'), (28, '2015-05-16 01:20:06', '2015-05-16 01:20:06', 'Manu', 'Ram', 'vasanthanpv1@gmail.com', 32, '55'), (30, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, '', '', 0, ''); |
if we use the below mysql query output as like below
|
1 2 3 |
SELECT id,case age when '' then 'No Age' else age end as sudent_ages,case first_name when '' then 'No First Name' else first_name end as sudent_first_name from students |
you can see the result with NULL value for the first_name field.
if you use the query as in a newformat ..with IFNULL condition it wil work. below query will work for you
SELECT id,case age when ” then ‘No Age’ else age
end as sudent_ages,IFNULL(first_name, ‘NO First Name’)as student_firstname
from students
