Sunday, October 22, 2006

Handy MySQL function

Here is a simple function that can help coping with wrong database design, when you have a mix of NULLs and blank fields meaning the same, no value, which they shouldn't.

  1. DELIMITER $$
  2. DROP FUNCTION IF EXISTS `test`.`is_initial` $$
  3. CREATE FUNCTION `test`.`is_initial` (f varchar(255)) RETURNS BOOL
  4. BEGIN
  5. SET @is_initial = false;
  6. CASE f
  7. WHEN NULL THEN SET @is_initial = TRUE;
  8. WHEN '' THEN SET @is_initial = TRUE;
  9. ELSE SET @is_initial = FALSE;
  10. END CASE;
  11. RETURN @is_initial;
  12. END $$
  13. DELIMITER ;

2 comments:

Anonymous said...

Just pointing out a typo for ya...

Line 11: #
WHEN NULL THEN SET @is_iniali = TRUE;

I'm guessing should be #
WHEN NULL THEN SET @is_initial = TRUE;

pabloj said...

Yes, typo corrected, thank you