Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: sorting strings in Alphanumeric in Oracle

    Hai !

    I have a table called test. it contains a cloumn named ID with a datatype is varchar2.

    It contains records as

    ID

    10
    20
    A30
    B20
    40
    15
    25.

    I want to retrive a record which is greater than 15. But not contains A30.

    Pl help

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    If you have a column which requires to be "sorted", then you should design your application to populate said column in the correct sort format.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Code:
    SQL> select id
      2    from test
      3   where replace( translate( id, '0123456789', rpad( '*', 10, '*' ) ), '*' ) is null
      4  /
    
    ID
    ----------
    10
    20
    40
    15
    25
    
    SQL> select id
      2    from (
      3  select id
      4    from test
      5   where replace( translate( id, '0123456789', rpad( '*', 10, '*' ) ), '*' ) is null
      6         )
      7   where id > 15
      8  /
    
    ID
    ----------
    20
    40
    25
    It would help if you create an fbi on
    Code:
    SQL> create index test_idx on test ( replace( translate( id, '0123456789', rpad( '*', 10, '*' ) ), '*' ) )
      2  /
    
    Index created.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Almost,
    use the following. If you don't convert to number then 144 would not be returned by "id > 15"

    Code:
    select id
     from (
     select to_number(id) id_num, id
     from test
     where replace( translate( id, '0123456789', '**********', '*' ) is null
           )
     where id_num > 15;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Not following you there. I just inserted 144 and it worked fine.
    Code:
    SQL> insert into test values ( 144 );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from test;
    
    ID
    ----------
    10
    20
    A30
    B20
    40
    15
    144
    25
    
    8 rows selected.
    
    SQL> select id
      2    from (
      3  select id
      4    from test
      5   where replace( translate( id, '0123456789', rpad( '*', 10, '*' ) ), '*' ) is null
      6         )
      7   where id > 15
      8  /
    
    ID
    ----------
    20
    40
    144
    25
    Allthough the returned datatype is of type varchar, the where clause cast it as number when applying the condition thus evaluate both as numbers.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your right, however I have never been comfortable with using implied conversion. If oracle changes the rules, it doesn't work. If you return and compare it as number, then it will always work.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    This will do it:
    Code:
    select id
      from (select id from test
             where trim(translate(id,'~0123456789','~')) is null)
     where id > 15
    /

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Just for fun, here's an ugly hack:

    Code:
    SELECT * FROM test
    WHERE LPAD(id,4,'Z') > LPAD(:var,4,'Z');
    It won't internationalise very well of course.

Posting Permissions

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