Set Empty Strings ('') to Null in the Whole Database

Set empty strings ('') to NULL in the whole database

The most efficient way to achieve this:

• Run a single UPDATE per table.
• Only update nullable columns (not defined NOT NULL) with any actual empty string.
• Only update rows with any actual empty string.
• Leave other values unchanged.

This related answer has a plpgsql function that builds and runs the UPDATE command using system catalog pg_attribute automatically and safely for any given table:

• Replace empty strings with null values

Using the function f_empty2null() from this answer, you can loop through selected tables like this:

DO
$do$
DECLARE
   _tbl regclass;
BEGIN
   FOR _tbl IN
      SELECT c.oid::regclass
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relkind = 'r'            -- only regular tables
      AND    n.nspname NOT LIKE 'pg_%'  -- exclude system schemas
   LOOP
      RAISE NOTICE $$PERFORM f_empty2null('%');$$, _tbl;
      -- PERFORM f_empty2null(_tbl);  -- uncomment to prime the bomb
   END LOOP;
END
$do$;

Careful! This updates all empty strings in all columns of all user tables in the DB. Be sure that's what you want or it might nuke your database.

You need UPDATE privileges on all selected tables, of course.

As a child safety device I commented the payload.

You may have noted that I use the system catalogs directly, not the information schema (which would work, too). About this:

• How to check if a table exists in a given schema
• Query to return output column names and data types of a query, table or view

For repeated use

Here is an integrated solution for repeated use. Without safety devices:

CREATE OR REPLACE FUNCTION f_all_empty2null(OUT _tables int, OUT _rows int) AS
$func$
DECLARE
   _typ CONSTANT regtype[] := '{text, bpchar, varchar, \"char\"}';
   _sql text;
   _row_ct int;
BEGIN
   _tables := 0;  _rows := 0;
   FOR _sql IN
      SELECT format('UPDATE %s SET %s WHERE %s'
                  , t.tbl
                  , string_agg(format($$%1$s = NULLIF(%1$s, '')$$, t.col), ', ')
                  , string_agg(t.col || $$ = ''$$, ' OR '))
      FROM  (
         SELECT c.oid::regclass AS tbl, quote_ident(attname) AS col
         FROM   pg_namespace n
         JOIN   pg_class     c ON c.relnamespace = n.oid
         JOIN   pg_attribute a ON a.attrelid = c.oid
         WHERE  n.nspname NOT LIKE 'pg_%'   -- exclude system schemas
         AND    c.relkind = 'r'             -- only regular tables
         AND    a.attnum >= 1               -- exclude tableoid & friends
         AND    NOT a.attisdropped          -- exclude dropped columns
         AND    NOT a.attnotnull            -- exclude columns defined NOT NULL!
         AND    a.atttypid = ANY(_typ)      -- only character types
         ORDER  BY a.attnum
         ) t
      GROUP  BY t.tbl
   LOOP
      EXECUTE _sql;
      GET DIAGNOSTICS _row_ct = ROW_COUNT;  -- report nr. of affected rows
      _tables := _tables + 1;
      _rows := _rows + _row_ct;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM pg_temp.f_all_empty2null();

Returns:

 _tables | _rows
---------+---------
 23      | 123456

Note how I escaped both table and columns names properly!

c.oid::regclass AS tbl, quote_ident(attname)  AS col

Consider:

• Table name as a PostgreSQL function parameter

Careful! Same warning as above.

Also consider the basic explanation in the answer I linked above:

• Replace empty strings with null values

Updating empty string to NULL for entire database

I figured out how to do this using a stored procedure. I'd definitely look at using a scripting language next time.

DROP PROCEDURE IF EXISTS settonull;

DELIMITER //

CREATE PROCEDURE settonull()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE _tablename VARCHAR(255);
  DECLARE _columnname VARCHAR(255);
  DECLARE cur1 CURSOR FOR SELECT 
                           CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name,
                           COLUMN_NAME AS column_name 
                           FROM information_schema.COLUMNS 
                           WHERE IS_NULLABLE = 'YES' 
                           AND TABLE_SCHEMA IN ('table1', 'table2', 'table3');

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO _tablename, _columnname;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @s = CONCAT('UPDATE ', _tablename, ' SET ', _columnname, ' = NULL WHERE LENGTH(TRIM(', _columnname, ')) = 0' );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE cur1;
END//

DELIMITER ;

CALL settonull();

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

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

Change all empty strings to NULL using one query with loop. SQL

@empty is empty. It doesn't represent the empty string when you concatenate it.

One simple solution is:

SET @empty = '''';

However, you can do the same thing by passing a parameter using sp_executesql. And I like that approach:

declare @cnt INT = 1959;
while @cnt < 2017
begin
   declare @sql nvarchar(1000)
   set @sql = 'UPDATE [HDProjEtap1Proba1].[dbo].[TESTGDP] SET [' + CAST(@cnt as nvarchar(255)) + '] = null WHERE ['+ CAST(@cnt as nvarchar(255)) +'] = @empty';
   exec sp_executesql @sql, N'@empty nvarchar(255)', @empty = '';
   set @cnt = @cnt + 1;
end;

Or event:

declare @cnt INT = 1959;
while @cnt < 2017
begin
   declare @sql nvarchar(1000)
   set @sql = '
UPDATE [HDProjEtap1Proba1].[dbo].[TESTGDP]
    SET [@colname] = null
    WHERE [@colname] = @empty';

   set @sql = replace(@sql, '@colname', cast(@cnt as varchar(255));

   exec sp_executesql @sql, N'@empty nvarchar(255)', @empty = '';
   set @cnt = @cnt + 1;
end;

Notice that in this version, the SQL statement is easy to read (and hence to modify and debug). It is defined with parameters that are then subsequently replaced.

Difference between NULL and Blank Value in Mysql

1. NULL is an absence of a value. An empty string is a value, but is just empty. NULL is special to a database.

2. NULL has no bounds, it can be used for string, integer, date, etc. fields in a database.

3. NULL isn't allocated any memory, the string with NULL value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".

How to use NULL or empty string in SQL

Select *
From Table
Where (col is null or col = '')

Or

Select *
From Table
Where IsNull(col, '') = ''

How to convert empty spaces into null values, using SQL Server?

Did you try this?

UPDATE table 
SET col1 = NULL 
WHERE col1 = ''

As the commenters point out, you don't have to do ltrim() or rtrim(), and NULL columns will not match ''.

Empty string vs NULL

Some differences between them:

• NULL can be assigned to any type, as opposed to empty string which won't be compatible with date/numerical fields.
• NULL is an UNKNOWN value, it doesn't have a value as opposed to an empty string, which is a value, but empty one.
• As far as I'm aware of, NULL shouldn't capture memory as opposed to an empty string which does.
• null = null will result in null as opposed to ''='' which will result in TRUE.

---

Related Topics