Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Unanswered: alphanumeric data comparisions

    Dear gurus,

    i have a table with following data:

    service_code

    svc90001
    svc90011
    svc90181
    svc000001
    svc1001
    svc2
    svc99
    svc80009
    svc89
    abc1
    def2
    mno78
    ..
    etc

    I want to get all the data in service_code column which are between given two given service_codes.
    Example:
    proc(svc1,svc99) should fetch
    svc1
    svc2
    svc89


    and not data inclunding

    svc90001
    svc90011
    svc90181
    ..
    ..

    note:-
    1.length of data in column is not fixed
    2.data is alphanumeric



    please tell me how to get the required data.

    Thanks in advance
    Last edited by vrsrinivas; 04-24-08 at 07:42.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think a combination of the SUBSTR and TO_NUMBER functions should do it for you.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2004
    Location
    india
    Posts
    67
    thanks for d reply...
    but can u implement this in a single query???

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

    Talking

    Maybe a combination of SUBSTR and LPAD would work better.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Is it possible? Certainly, it is. Here's an example of a procedure which does the job (as you've said that proc(svc1, svc99) should fetch ...):
    Code:
    SQL> select * from test;
    
    SC
    --------------------
    svc90001
    svc90011
    svc90181
    svc1001
    svc2
    svc99
    svc80009
    svc89
    abc1
    def2
    mno78
    
    11 rows selected.
    A test or two:
    Code:
    SQL> var rc refcursor
    SQL>
    SQL> exec prc_sc ('svc1', 'svc99', :rc);
    
    PL/SQL procedure successfully completed.
    
    SQL> print rc
    
    SC
    --------------------
    svc2
    svc99
    svc89
    
    SQL> exec prc_sc ('svc90000', 'svc92000', :rc);
    
    PL/SQL procedure successfully completed.
    
    SQL> print rc
    
    SC
    --------------------
    svc90001
    svc90011
    svc90181
    
    SQL>
    Here's the procedure code:
    Code:
    CREATE OR REPLACE PROCEDURE prc_sc 
      (par_1 IN CHAR, par_2 IN CHAR, cur OUT sys_refcursor) 
    IS
    BEGIN
      OPEN cur FOR
      SELECT t.sc
        FROM TEST t
        WHERE REGEXP_SUBSTR(sc, '[[:alpha:]]+') 
              BETWEEN REGEXP_SUBSTR(par_1, '[[:alpha:]]+')
                  AND REGEXP_SUBSTR(par_2, '[[:alpha:]]+')					
          AND TO_NUMBER(REGEXP_SUBSTR(sc, '[[:digit:]]+'))
              BETWEEN TO_NUMBER(REGEXP_SUBSTR(par_1, '[[:digit:]]+'))
                  AND TO_NUMBER(REGEXP_SUBSTR(par_2, '[[:digit:]]+'));
    END;
    /
    If you want to implement it in a single query, just extract the SELECT statement out of the procedure and slightly modify use of IN parameters.

    Now, as you didn't say which database version you use, if it is lower than 10g, you won't be able to use such a solution as there were no regular expressions in earlier versions. That might, however, be good for you as you'll have a chance to practice and write a similar query/procedure using Oracle functions other Forum members suggested.

  6. #6
    Join Date
    Feb 2004
    Location
    india
    Posts
    67
    Thanks evrybody..

    Thanks a lot LittleFoot, for Im using 10g... this shud surely solve my problem....

Posting Permissions

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