HELP : empty string to Null conversion by SQL

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHNHi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHN

Dunno how you're going to do it without naming column names.

UPDATE MyTable
SET Col1 = Null, Col2 = Null, Col3 = Null
WHERE [insert where clause here]

Note that this will set Col1, Col2 and Col3 to Null; not just where the are equal to an empty string, but where there are values as well.

You may consider the CASE statement to selectively change blank values to NULL.

Regards,

hmscott|||As an alternate consideration, you might try:

SELECT
'UPDATE [' + TABLE_NAME + '] SET [' + COLUMN_NAME + '] = NULL
WHERE [' + COLUMN_NAME + '] = '''
FROM INFORMATION_SCHEMA.columns
WHERE DATA_TYPE IN ('char','nchar','varchar','nvarchar')

This will give you a bunch of individualized UPDATE statements. You would then have to run each statement seperately.

Regards,

hmscott|||Ok, but please use:
set [YourColumn] = NULLIF([YourColumn], '')
...or you will wipe out all your data.