Post reply

replace Nulls with blanks across an entire table

  • August 22, 2011 at 9:03 am

    #246620

    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

  • August 22, 2011 at 9:13 am

    #1371470

    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 t

    inner join sys.columns sc

    on t.object_id = sc.object_id

    where 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!

  • August 22, 2011 at 9:22 am

    #1371479

    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.

  • August 22, 2011 at 9:27 am

    #1371483

    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!

  • August 22, 2011 at 9:38 am

    #1371489

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply