Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Arrow Unanswered: 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 JOHN

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by john_wsm
    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 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
    Have you hugged your backup today?

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    As an alternate consideration, you might try:

    Code:
    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
    Have you hugged your backup today?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ok, but please use:
    set [YourColumn] = NULLIF([YourColumn], '')
    ...or you will wipe out all your data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •