restockit
asked on
Replace null with blank
what query do i need to write to replace all fields in a table that have null in them with blank?
update TableName
Set FieldName = ''
where FieldName = NULL
do it for all Comulmns
Set FieldName = ''
where FieldName = NULL
do it for all Comulmns
ASKER
Is there a way to do all the columns in the table at the same time?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sure,
UPDATE YourTableName
SET YourCol1 = '', YourCol2 = '', YourCol3 = '', ...
WHERE YourCol1 IsNull Or YourCol2 Is Null Or YourCol3 Is Null Or ...
UPDATE YourTableName
SET YourCol1 = '', YourCol2 = '', YourCol3 = '', ...
WHERE YourCol1 IsNull Or YourCol2 Is Null Or YourCol3 Is Null Or ...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
warning: you can lose data with acperkins suggestion: if YourCol1 has a non-null value but YourCol2 is null, the update statement will replace the value in YourCol1 with a blank (always run updates as a select first to see what it's going to do). An alternative would be:
UPDATE YourTableName
SET YourCol1 = ISNULL(YourCol1,''), YourCol2 = ISNULL(YourCol2,''), YourCol3 = ISNULL(YourCol3,''), ...
WHERE YourCol1 IsNull Or YourCol2 Is Null Or YourCol3 Is Null Or ...
What you may be looking for is not allowing nulls:
-disallow nulls for each column in the design view of the table
-assign a default value of '' or 0 (all nulls will automatically be replaced)
-any new records will be free of nulls as well
this will ensure there are never nulls in your table and you won't ever have to deal with them!
MDD
UPDATE YourTableName
SET YourCol1 = ISNULL(YourCol1,''), YourCol2 = ISNULL(YourCol2,''), YourCol3 = ISNULL(YourCol3,''), ...
WHERE YourCol1 IsNull Or YourCol2 Is Null Or YourCol3 Is Null Or ...
What you may be looking for is not allowing nulls:
-disallow nulls for each column in the design view of the table
-assign a default value of '' or 0 (all nulls will automatically be replaced)
-any new records will be free of nulls as well
this will ensure there are never nulls in your table and you won't ever have to deal with them!
MDD
Aneesh's solution is very good (I'd modify it to "SELECT DISTINCT COALESCE..."), coupled with the suggestions added by mdefehr (alter the table to add default values, et cetera). I'd split points, 60/40. This is just a comment.
Good point.
ASKER
thanks guys
SET UrField = ''
WHERE urField IS NULL