Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: Find if there is a character in a field

    Hi all,

    I am trying to see if there is a non-numeric value in a varchar2 field - am not very good and tried translate function to no avail.

    Would anyone have an easy way to get the rows.

    Rgs,
    Breen.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could use TRANSLATE to remove all the digits, and see if anything remains:

    WHERE TRANSLATE ( col, 'x0123456789', 'x' ) IS NULL

    If you need to know that the column contains a valid number (e.g. 1.234 but not 1.2.3.4) then you would need a function like:
    Code:
    create or replace function is_number(x in varchar2) return integer
    is
      n number;
    begin
      n := to_number(x);
      return 1;
    exception
      when others then
        return 0;
    end;
    Then you can use it in SQL like this:

    WHERE is_number(col) = 1

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanks Tony,

    Actually just got it there, it's not perfect but it got me the rows in question:

    select FK_CSF_FILE_NUMBER,CALL_START_TIME
    from ICTPRDB.PRICING_INPUT_CDR
    where rtrim(ltrim(TRANSLATE(CALL_START_TIME,'1234567890' ,' '))) is not null;

    Cheers,
    Breen.

Posting Permissions

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