Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Posts
    12

    Unanswered: Regular Expression

    Hi,

    I have longitude column of type varchar2(3). Most of the data is numeric. But there are erronous data which have characters in them. Following is a sample of the data.
    LON
    ---
    94
    95
    95.
    96
    97
    98
    98h
    99
    BA
    ER
    OF

    I need to write a sql statement which returns logitude values where all the characters are between 0 to 9. A regular expression for the crieteria could be [0-9]. How can I do this in Oracle? The LIKE operator does not facilitate this.

    Thank you,

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: Regular Expression

    Assuming, tname is the column name in the table :

    select translate(tname,'0123456789abcdefghijklmnopqrstuvw xyzABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
    from your_table
    /

    Originally posted by amar_das
    Hi,

    I have longitude column of type varchar2(3). Most of the data is numeric. But there are erronous data which have characters in them. Following is a sample of the data.
    LON
    ---
    94
    95
    95.
    96
    97
    98
    98h
    99
    BA
    ER
    OF

    I need to write a sql statement which returns logitude values where all the characters are between 0 to 9. A regular expression for the crieteria could be [0-9]. How can I do this in Oracle? The LIKE operator does not facilitate this.

    Thank you,

Posting Permissions

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