SQL Server Performance

Replace space/empty field entry with null....

Discussion in 'General Developer Questions' started by pmchugh, Feb 16, 2005.

  1. pmchugh New Member

    I have a file that I will be importing that may contain blnk values for some of the fields.

    I need to identify the blank fields and set them to null.

    Thanks in advance for any help!!!
  2. Luis Martin Moderator

    Did you import that file to a table and after you want to do that, or you need when you are importing?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. ranjitjain New Member

    Hi pmchung,
    U have not described ur query in detail.
    After importing u can update flds. or while importing into table
    u can fire ur query too.
  4. dineshasanka Moderator

    quote:Originally posted by pmchugh

    I have a file that I will be importing that may contain blnk values for some of the fields.

    I need to identify the blank fields and set them to null.

    Thanks in advance for any help!!!

    I believe you are using DTS for this . If so u can link the fields so that other fields will automatically set to null.
  5. Adriaan New Member

    In case you're using an INSERT INTO ... SELECT MyColumn type of statement, you could use:
    SELECT CASE WHEN MyColumn = '' THEN NULL ELSE MyColumn END
    Make sure the target table accepts NULL on the column.
  6. pmchugh New Member

    Ok - I've been looking at using DTS.
    In this example, I'd be moving the data from a Flat Fixed Length record to the DB row.
    It looks like DTS will give me the following:

    '**********************************************************************
    ' Visual Basic Transformation Script
    ' Copy each source column to the
    ' destination column
    '************************************************************************

    Function Main()
    DTSDestination("CustomerID") = DTSSource("CustomerID")
    DTSDestination("CompanyName") = DTSSource("CompanyName")
    DTSDestination("ContactName") = DTSSource("ContactName")
    DTSDestination("ContactTitle") = DTSSource("ContactTitle")
    DTSDestination("Address") = DTSSource("Address")
    DTSDestination("City") = DTSSource("City")
    DTSDestination("Region") = DTSSource("Region")
    DTSDestination("PostalCode") = DTSSource("PostalCode")
    DTSDestination("Country") = DTSSource("Country")
    DTSDestination("Phone") = DTSSource("Phone")
    DTSDestination("Fax") = DTSSource("Fax")
    Main = DTSTransformStat_OK
    End Function

    My question is where do I put in the test and the assignment to Null?

    For example:

    If DTSSource("ContactName") NOT > "Space"
    DTSDestination("ContactName") = NULL

    Or something like that....

    I'm looking thru the Help but I do not see this addressed.

    Thanks again for your responses.....
  7. Adriaan New Member

    Not sure about your scripting language, but I would expect that it supports IF ... THEN ... ELSE ... END IF:

    If DTSSource("ContactName") = "" Then
    DTSDestination("ContactName") = NULL
    Else
    DTSDestination("ContactName") = DTSSource("ContactName")
    End If
  8. ranjitjain New Member

    check for zero length string---
    if len("ContactName")>0 then "ContactName"
    else null
    or
    u can fire query after inserting like
    update table set field1=null where field1=''
  9. pmchugh New Member

    Thanks for the suggestions.

    But, where do I put the code?

    In the previous example I included some VB Script code that was generated by DTS.
    Can I just just jump in and modify that code?

    I would believe that it would accept VB syntax.

    Does anyone have a link/URL to any documentation on how this works with DTS?

    Thanks again for your responses....

Share This Page