Sql-server – Update “NULL” string to Actual NULL value

nullsql server

I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like

where date is null

it will not return the fields that have the "NULL" string.

What I am needing to do is run an update of the whole table that will convert all string "NULLS" to the actual NULL value. The "NULL" strings happen throughout all columns of the table so it is not just 1 column that needs to be updated. I am not sure how to approach this scenario. I'm thinking I might need to use a loop since i have many columns but then again there might be a simple solution without having to use a loop. What would be the best way to resolve this issue?

Best Answer

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

Related Solutions

PostgreSQL: count datasets / lines containing NULL values

You will need to use dynamic SQL to acheive this, perhaps like this:

testbed:

create role stack;
create schema authorization stack;
set role stack;

create table my_table as 
select generate_series(0,9) as id, null::integer as val;

create table my_table2 as 
select g as id, case when g<=5 then null::integer else 1 end as val
from generate_series(0,9) g;

function:

create function get_with_nulls_rowcount(p_schema in text, p_table in text) 
                returns integer language plpgsql as $$
declare 
  n integer;
  s text;
begin
  --
  select array_to_string(array_agg(column_name::text||' is null'), ' or ')
  into s
  from information_schema.columns
  where table_schema=p_schema and table_name=p_table;
  --
  execute 'select count(*) from '||p_table||' where '||s into n;
  return n;
end;$$;

query:

select table_schema, table_name, 
       get_with_nulls_rowcount(table_schema, table_name)
from information_schema.tables
where table_schema='stack';

result:

 table_schema | table_name | get_with_nulls_rowcount
--------------+------------+-------------------------
 stack        | my_table   |                      10
 stack        | my_table2  |                       6
(2 rows)

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps

Best Answer

Related Solutions

PostgreSQL: count datasets / lines containing NULL values

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Related Question