Menu
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?
799
There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.
If you've opened a table and you want to clear an existing value to NULL, click on the value, and press Ctrl + 0 .
UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0.
ISNULL Function in SQL Server To use this function, all you need to do is pass the column name in the first parameter and in the second parameter pass the value with which you want to replace the null value. So, now all the null values are replaced with No Name in the Name column.
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
177
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
