How do I change all empty strings to NULL in a table?

Kyle_West_34133 0
Viewed 42046

I have a legacy table with about 100 columns (90% nullable). In those 90 columns I want to remove all empty strings and set them to null. I know I can:

update table set column = NULL where column = '';
update table set column2 = NULL where column2 = '';

But that is tedious and error prone. There has to be a way to do this on the whole table?

sql-update mysql sql string
7 Answers

Hammerite's answer is good but you can also replace CASE statements with IFs.

UPDATE
    TableName
SET
    column01 = IF(column01 = '', NULL, column01),
    column02 = IF(column02 = '', NULL, column02),
    column03 = IF(column03 = '', NULL, column03),
    ...,
    column99 = IF(column99 = '', NULL, column99)
user_76367540

You could write a simple function and pass your columns to it:

Usage:

SELECT
  fn_nullify_if_empty(PotentiallyEmptyString)
FROM
  table_name
;

Implementation:

DELIMITER $$
CREATE FUNCTION fn_nullify_if_empty(in_string VARCHAR(255))
  RETURNS VARCHAR(255)
  BEGIN
    IF in_string = ''
      THEN RETURN NULL;
      ELSE RETURN in_string;
    END IF;
  END $$
DELIMITER ;
user_70328560
Related