CODEWITHSUNDEEP
oracle-databaseoracle11g
sach
sach

Reputation: 127

Oracle regular expression for Alphanumeric check

We have below table structure.

From    To    Output
A001    A555  "A"
A556    A999  "B"
AA01    AA55  "C"
AA56    AA99  "D"
B001    B555  "C"

If my input value if greater than From column and less than To column then return that Output. E.g. If I received A222 then return output as "A". If I received AA22 then return output as "C".

Currently I have handle this in Java but wanted to check if I can write a Oracle query(using Regex) to get above output.

Thanks Sach

Upvotes: 0

Views: 725

Answers (2)

MT0
MT0

Reputation: 168806

Create your table with the appropriate (virtual) columns and indexes:

Oracle Setup:

CREATE TABLE table_name (
  "from"       VARCHAR2(10) UNIQUE,
  "to"         VARCHAR2(10) UNIQUE,
  output       CHAR(1)      NOT NULL,
  prefix       VARCHAR2(9)  GENERATED ALWAYS AS (
                              CAST(
                                REGEXP_SUBSTR( "from", '^\D+' )
                                AS VARCHAR2(9)
                              )
                            ) VIRTUAL,
  from_postfix NUMBER(9)    GENERATED ALWAYS AS (
                             TO_NUMBER( REGEXP_SUBSTR( "from", '\d+$' ) )
                            ) VIRTUAL,
  to_postfix   NUMBER(9)    GENERATED ALWAYS AS (
                              TO_NUMBER( REGEXP_SUBSTR( "to", '\d+$' ) )
                            ) VIRTUAL,
  CONSTRAINT table_name__from_to__u  PRIMARY KEY (
    prefix, from_postfix, to_postfix
  ),
  CONSTRAINT table_name__f_t_prefix__chk CHECK (
    REGEXP_SUBSTR( "from", '\^\D+' ) = REGEXP_SUBSTR( "to", '\^\D+' )
  )
);

INSERT INTO table_name ( "from", "to", output )
SELECT 'A001', 'A555', 'A' FROM DUAL UNION ALL
SELECT 'A556', 'A999', 'B' FROM DUAL UNION ALL
SELECT 'AA01', 'AA55', 'C' FROM DUAL UNION ALL
SELECT 'AA56', 'AA99', 'D' FROM DUAL UNION ALL
SELECT 'B001', 'B555', 'C' FROM DUAL;

COMMIT;

Query:

Then your query can use the index and not need to do a full table scan:

SELECT output
FROM   table_name
WHERE  REGEXP_SUBSTR( :input, '^\D+' ) = prefix
AND    TO_NUMBER( REGEXP_SUBSTR( :input, '\d+$' ) )
         BETWEEN from_postfix
         AND     to_postfix;

Output:

If the input bind variable is AA22 then the result is:

OUTPUT
------
C

Explain Plan:

PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 2408507965
------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT            |                        |     1 |    35 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_NAME             |     1 |    35 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TABLE_NAME__FROM_TO__U |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------- 
       2 - access("PREFIX"= REGEXP_SUBSTR (:INPUT,'^\D+') AND "TO_POSTFIX">=TO_NUMBER(
              REGEXP_SUBSTR (:INPUT,'\d+$')) AND "FROM_POSTFIX"<=TO_NUMBER( REGEXP_SUBSTR (:INPUT,'\d+$')))
       filter("TO_POSTFIX">=TO_NUMBER( REGEXP_SUBSTR (:INPUT,'\d+$')))

Upvotes: 1

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

You have to extract varchar and number value from columns and input value and compare it each other

with tabl("FROM","TO",Output) as (
select 'A001','A555','A' from dual union all
select 'A556','A999','B' from dual union all
select 'AA01','AA55','C' from dual union all
select 'AA56','AA99','D' from dual union all
select 'B001','B555','C' from dual)
select * from tabl
where to_number(regexp_substr('AA22','[0-9]+')) between to_number(regexp_substr("FROM",'[0-9]+')) and to_number(regexp_substr("TO",'[0-9]+'))
and regexp_substr('AA22','[^0-9]+') = regexp_substr("FROM",'[^0-9]+')

Upvotes: 0

Related Questions