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

Question

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? Thanks!

Kyle

Answer 1

I think you'll need to pull each row into a language like C#, php, etc.

Something like:

rows = get-data()
foreach row in rows
    foreach col in row.cols
        if col == ''
            col = null
        end if
    next
next
save-data()

Answer 2

There isn't a standard way - but you can interrogate the system catalog to get the relevant column names for the relevant table and generate the SQL to do it. You can also probably use a CASE expression to handle all the columns in a single pass - a bigger SQL statement.

UPDATE Table
   SET Column1 = CASE Column1 = ' ' THEN NULL ELSE Column1 END,
       ...

Note that once you've generated the big UPDATE statement, all the work is done down in the server. This is much more efficient than selecting data to the client application, changing it there, and writing the result back to the database.

Answer 3

UPDATE
    TableName
SET
    column01 = CASE column01 WHEN '' THEN NULL ELSE column01 END,
    column02 = CASE column02 WHEN '' THEN NULL ELSE column02 END,
    column03 = CASE column03 WHEN '' THEN NULL ELSE column03 END,
    ...,
    column99 = CASE column99 WHEN '' THEN NULL ELSE column99 END

This is still doing it manually, but is slightly less painful than what you have because it doesn't require you to send a query for each and every column. Unless you want to go to the trouble of scripting it, you will have to put up with a certain amount of pain when doing something like this.

Edit: Added the ENDs

Answer 4

One possible script:

for col in $(echo "select column_name from information_schema.columns
where table_name='$TABLE'"|mysql --skip-column-names $DB)
do
echo update $TABLE set $col = NULL where $col = \'\'\;
done|mysql $DB