replace Nulls with blanks across an entire table
-
Does anyone know of a a way to replace "null" with "" across an entire table without having to list each column one by one.
Many Thanks
-
nope not possible. a column must be identified, and it's new value identified for a valid UPDATE TABLE statement.
...but...you can use the table's metadata to generate the commands for you.
SELECT 'UPDATE ' + quotename(t.name) + ' SET ' + quotename(sc.name) + ' = '''' WHERE ' + quotename(sc.name) + ' IS NULL 'FROM sys.tables tinner join sys.columns scon t.object_id = sc.object_idwhere type_name(sc.system_type_id) in( 'varchar','char','nvarchar','nchar')Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I would ignore the where completely unless I'd know only a small % of the rows would get updated.
This will do a table scan anyways. So you might as well make it count and do all columns in 1 scan instead of 1 scan per column.
-
Ninja's_RGR'us (8/22/2011)
I would ignore the where completely unless I'd know only a small % of the rows would get updated.This will do a table scan anyways. So you might as well make it count and do all columns in 1 scan instead of 1 scan per column.
i thought the WHERE would be requires so you don't simply erase pre-existing data. it's not so much to try to avoid the table scan, but to set NULLS to empty string only where it's appropriate.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I type slower than I think.
ISNULL(Colname, '')
If this was not a one time deal I would put a bunch of ors in the where to only update the rows that need it and lessen the blow a little bit on the server.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply