One of our senior developers asked the Oracle DBAs to execute this query, attached. Rather than doing fifteen full-table scans, I thought I'd try it with REGEXP_LIKE. Well, I'm looked at Oracle and other documentation for a good two hours without catching my error(s). I would appreciate a fresh pair of eyes, please. My most recent effort is
Select * from MYSCHEMA.CNSTR_NBR
where REGEXP_LIKE(CNST_NR_ID, 'WF20(2|3|6)02[2-6]%')
order by CNST_NR_ID desc;
Looking at the original script in the code snippet, the match pattern clearly shows four elements: the string WF20, character 5 has to be 2,3, or 6; a second string 02, and a final match on 2 through 6 inclusive. The trailing characters in the column should be ignored. TIA for taking a look.
Select * from MYSCHEMA.CNSTR_NBR
where CNST_NR_ID LIKE ('WF206022%')
or CNST_NR_ID LIKE ('WF206023%')
or CNST_NR_ID LIKE ('WF206024%')
or CNST_NR_ID LIKE ('WF206025%')
or CNST_NR_ID LIKE ('WF206026%')
or CNST_NR_ID LIKE ('WF203022%')
or CNST_NR_ID LIKE ('WF203023%')
or CNST_NR_ID LIKE ('WF203024%')
or CNST_NR_ID LIKE ('WF203025%')
or CNST_NR_ID LIKE ('WF203026%')
or CNST_NR_ID LIKE ('WF202022%')
or CNST_NR_ID LIKE ('WF202023%')
or CNST_NR_ID LIKE ('WF202024%')
or CNST_NR_ID LIKE ('WF202025%')
or CNST_NR_ID LIKE ('WF202026%')
order by CNST_NR_ID desc;
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
TRUSTED BY
Instead of using '%', use '.*'. In regular expressions that means match any character, any number of times (including zero).