Stack Exchange
How to extract numeric value in alphanumeric string using given input parameter
I have been trying to add a filtering condition based on the user input numeric value for a column having an alphanumeric string, and it takes a time around 25 minutes to return the results.
I have tried reg_exp() and SUBSTR() methods also within pl/sql developer.
User will seach using values like 6388997 and it should return EMPLOYEE_ID: 638997
SELECT EMP_NUM FROM ABC.HR_ORG_EMPLOYEE_DATA
WHERE SUBSTR(EMP_NUM ,12,14)= TO_CHAR('%' || '&EMP' || '%') 

SELECT EMP_NUM FROM ABC.HR_ORG_EMPLOYEE_DATA
WHERE regexp_replace(EMP_NUM , '[^0-9]', '') = '%' || '&EMP' || '%'

This is a simple snapshot of the HR_ORG_EMPLOYEE_DATA table




EMP_NUM




EMPLOYEE_ID: 448696


SEQ_NO     : 638997


Address_NO : 778980




Do we have any efficient ways of resolving these types of queries?

Can't you just simple fetch the entire substring after the last : ?

Why do you keep all that ("EMPLOYEE_ID: 448696") in the same column? Column name should be EMPLOYEE_ID, its value 448696 and there should be index on that column. Then you'd just ... where employee_id = &emp

@Littlefoot That was the original structure of this table, Yes I think it would be better if we could redesign the structure.

Oracle's regular expression functions tend to be CPU intensive versus alternatives.
An alternative not using regular expressions would be the following

 create table t (
  col varchar2(50)
); 

 insert into t values ('EMPLOYEE_ID: 448696'); 

 insert into t values ('EMPLOYEE_ID: 448'); 


SELECT SUBSTR(col, INSTR(col, ':') + 2) AS emp_num FROM t

EMP_NUM
448696
448



Hi, @Beefstu I followed your approach too, it took me around 25 minutes to return the results. This table has records of about 295,000

In addition to pointing you to the above comments by @MTO, LittleFoot who are Oracle geniuses you may want to generate an explain plan and a SQL trace to see what is going on

If your data always has the same format EMPLOYEE_ID: XXXXXX and you want to use substitution variables then you do not need a "wildcard" match or to use sub-strings and can use:
SELECT EMP_NUM
FROM   ABC.HR_ORG_EMPLOYEE_DATA
WHERE  EMP_NUM = 'EMPLOYEE_ID: &EMP';

If it is part of a longer string then use LIKE:
SELECT EMP_NUM
FROM   ABC.HR_ORG_EMPLOYEE_DATA
WHERE  EMP_NUM LIKE '%EMPLOYEE_ID: &EMP%';

If you want to match the number anywhere in the string then:
SELECT EMP_NUM
FROM   ABC.HR_ORG_EMPLOYEE_DATA
WHERE  EMP_NUM LIKE '%&EMP%';


Thanks for the answer. Unfortunately, it has different types of formats like EMPLOYEE_ID, PERSON_ID, and the user needs to display the result even with the partial numeric input

@dgcharitha In that case, use EMP_NUM LIKE '%&EMP%'. (Updated)

I tried them too @MT0 it's taking like 25 minutes to return the results
424205 (6 digit integer) Search          - 25 Min 40 Sec
42420 (5 digit integer) Search            - 25 Min 32 Sec
4242 (4 digit integer) Search              - 13 Min 28 Sec
424  (3 digit integer) Search               - 2Min 12 Sec

@dgcharitha If you are looking for millions of output rows in a table with billions of input rows and don't have any indexing and are looking for sub-string matches then finding that the query is not performant is not unexpected. You probably need to extract the relevant text so that it is in its own column and (depending on cardinality) add an index and then you may be able to search it faster. But if you are asking generic performance questions then you have left a lot out of your question (including size of tables, indexes, execution plans, etc.).
