Results 1 to 4 of 4

Thread: Display only number values from a varchar column in oracle

  1. #1
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    514

    Display only number values from a varchar column in oracle

    i have a varchar column in the table. It contains both strings and numbers. Now I want display only pure numeric values from this column.

    Examples data is provided below:

    Code:
    col
    ------
    ABxy
    1234
    56a
    mnop
    327
    The output should contain only the below values from the column.

    Code:
    col
    ------
    1234
    327

    Please provide an sql query for this one.

  2. #2
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    514
    Oracle provides a regular expression version of like. You can use this to get only number from a varchar field.

    The sql query is

    Code:
    select col from table_name where regexp_like(col,'^[0-9]$');
    you can also get the values that are not numbers by using a "not" in the where clause. The complete sql query is:

    Code:
    select col from table_name where not regexp_like(col,'^[0-9]$');

  3. #3
    Junior Member
    Join Date
    Aug 2012
    Posts
    1

    Smile

    The code below mentioned is not generic.. It will not work in all the cases.. You can use my query to get number from varchar field:

    SELECT *
    FROM table_name
    where regexp_like(col,
    '^(\+|\-)?((\d+\.\d*)|(\d+)|(\.\d+))(E(\+|\-)?\d+)?$','i');

    If you want to get only Varchar values use this:

    SELECT *
    FROM table_name
    where not regexp_like(col,
    '^(\+|\-)?((\d+\.\d*)|(\d+)|(\.\d+))(E(\+|\-)?\d+)?$','i');


    Quote Originally Posted by vijay View Post
    Oracle provides a regular expression version of like. You can use this to get only number from a varchar field.

    The sql query is

    Code:
    select col from table_name where regexp_like(col,'^[0-9]$');
    you can also get the values that are not numbers by using a "not" in the where clause. The complete sql query is:

    Code:
    select col from table_name where not regexp_like(col,'^[0-9]$');

  4. #4
    Member
    Join Date
    May 2012
    Posts
    93
    You can also try this:
    Code:
    SELECT CASE WHEN  length(translate(col,0123456789,'&'))=length(col) 
                        THEN 1 -- contains numerals
                        ELSE  0 -- purely alphanumeric
                        END   if_contains_numerals
    FROM   table_name

Similar Threads

  1. Replies: 0
    Last Post: 12-16-2012, 01:05 AM
  2. Display multiple highest values in Oracle
    By Oracleguy in forum Oracle
    Replies: 0
    Last Post: 12-13-2012, 10:47 PM
  3. Replies: 1
    Last Post: 05-28-2012, 04:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

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