Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: replacing a VARCHAR value with NULL -10g

    Hi

    I've got a column in a table which only accepts NUMBERs but the value for that column is VARCHAR in the CSV file. I can't change the data type of that column because it's already got data in it so I thought maybe I can convert that VARCHAR value to NULL (as I don't really need that VARCHAR value).

    I tried:

    Code:
    to_number(null)
    &
    Code:
    to_number('')
    but nothing seems to work?

    Regards
    Shajju

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't bother using a function, just use a constant NULL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is not clear to me what you have, what you do, or what you want.

    If you are loading data using SQL*Loader & desire to ignore a column in the data file, you can specify FILLER to have it ignored.

    Otherwise you have done similar to below.
    See a picture of my car.
    It does not work.
    tell me how to make my car go.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Thanks for replying guys.

    constant null?

    filler?

    could you please give examples?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    I've got a column in a table which only accepts NUMBERs but the value for that column is VARCHAR in the CSV file. I can't change the data type of that column because it's already got data in it so I thought maybe I can convert that VARCHAR value to NULL (as I don't really need that VARCHAR value).
    Code:
    update the_table
       set the_wrongly_type_column = null
    where ...;
    works for any data type
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Thanks.

    Just wondering, can I specify:

    case (the value is not a number, then insert NULL into that column)

    i.e.,
    case (columnn <> number then null)

    or something?

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you could in an app program or stored proc, but not in a load function. Though there may be some vendor tools out there for that. another option might be to store that raw data for that column with a trigger that applies the number logic for filling in the numeric column upon insert.
    Dave

Posting Permissions

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