Results 1 to 3 of 3

Thread: test of invalid characters

  1. #1
    Join Date
    Jan 2003
    Location
    Melbourne, Australia
    Posts
    5

    test of invalid characters

    I'm attempting to identify invalid characters stored in a varchar2 column.

    Despite it's format, it represents the invoice number in Oracle Receivables.

    Unfortunately, I am trying to do a comparison between the 'number' stored and a range of numbers. Because there are a few rows with other ascii characters stored, I'm getting an ora-01722: invalid number returned.

    Does anyone have a script which can check for those records with an invalid value?

  2. #2
    Join Date
    Mar 2003
    Location
    CA,USA
    Posts
    18
    Hi Warrick

    I think the best thing to do is to write a program yourself and maintain it for your future use as well.

    Well, if you are interested in checking whether the VARCHAR2 column has any non-numeric characters, then I would write a program to compare the ascii equivalent of each character in the data (of a column for each row) with a the ascii codes of numbers 0 through 9 (ascii equivalent 49 through 57), if there is no match, then obviously the character is non-numeric.

    Hope that helps.

  3. #3
    Join Date
    Jan 2003
    Location
    Melbourne, Australia
    Posts
    5
    Thank you. This is what I had already persued.

    Unfortunately, with nearly a million records to parse, this is going to take a significant amount of time and is needed for several columns...

Posting Permissions

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