Search for a specific value in a column in Oracle

Question

Hello,

I have a column MetaKeys in my employee table. It contains a value like the one shown below:

MetaKeys="DocType=1***SubDocType=2***MinValue=123"

How do I query my table and fetch just the SubDocType value from MetaKeys column?

Select * from employee where Metakeys contains SubDocType=2

Answer 1

SELECT * FROM employee WHERE Metakeys LIKE '%SubDocType=2%'

It's not the most efficient way of doing queries because you're not going to get any indexing benefits, but it works.

Answer 2

I agree with Martin Smith that you may need to normalize this information out. That being said, here is one possible solution that may work for you:

SELECT SUBSTR (expr, INSTR (expr, '=') + 1) subdoctypeval
  FROM (SELECT REGEXP_SUBSTR ('DocType=1***SubDocType=2***MinValue=123',
                              'SubDocType=[^*]+',
                              1,
                              1)
                   expr
          FROM DUAL)

Answer 3

The correct thing to do is fix the design, so that there's not a metakeys column in the EMPLOYEE table, but instead an EMPLOYEE_METAKEY table:

CREATE TABLE EMPLOYEE_KEY_VALUES
(EMP_ID          number,
 EMP_METAKEY     VARCHAR2(100),
 EMP_METAVALUE   VARCHAR2(1000),
 constraint EMPLOYEE_METAKEYS_PK primary key (EMP_ID, EMP_METAKEY) using index,
 constraint EMPLOYEE_METAKEYS_FK01 foreign key (EMP_ID) 
   references EMPLOYEE (EMP_ID) on delete cascade
);

This design won't have the performance problem of having to trawl through the employee table when doing a metakey search, and you won't have to write regexes when looking for an employee with multiple key-value pairs.