not understanding regexp_like in Oracle

Question

I have define for example below in my With clause in query:

'.+d\$' as sec_level_pattern from dual

And now i can see below condition in select query:

not regexp_like(name,sec_level_pattern)

name is column from my rd_tst table.

What does the condition regexp_like checks here ? I have seen few question about regexp_like but did not understand what actually will check in my use case here.

Answer 1

what did i need to change for sec_level_pattern to return all the name ?

Your sample data seems to have the format:

• Start of the string;
• Two upper-case alphabetic characters;
• Ten upper-case alpha-numeric characters;
• The characters .GTX; then
• The end of the string.

You want a regular expression that matches the same things such as:

SELECT *
FROM   table_name
WHERE  REGEXP_LIKE( names, '^[A-Z]{2}[A-Z0-9]{10}\.GTX$' );

Which for your sample data:

CREATE TABLE table_name ( Names ) AS
SELECT 'AT0000730007.GTX' FROM DUAL UNION ALL
SELECT 'CH0012032048.GTX' FROM DUAL UNION ALL
SELECT 'DE0005550602.GTX' FROM DUAL UNION ALL
SELECT 'DE0007236101.GTX' FROM DUAL UNION ALL
SELECT 'DE000A1EWWW0.GTX' FROM DUAL UNION ALL
SELECT 'DE000ENAG999.GTX' FROM DUAL UNION ALL
SELECT 'DE000TUAG000.GTX' FROM DUAL UNION ALL
SELECT 'FI0009000681.GTX' FROM DUAL UNION ALL
SELECT 'FR0000120172.GTX' FROM DUAL UNION ALL
SELECT 'FR0000125007.GTX' FROM DUAL UNION ALL
SELECT 'IT0003132476.GTX' FROM DUAL UNION ALL
SELECT 'NL0000235190.GTX' FROM DUAL UNION ALL
SELECT 'NL0011794037.GTX' FROM DUAL UNION ALL
SELECT 'SE0000148884.GTX' FROM DUAL;

Returns all the names:

| NAMES            |
| :--------------- |
| AT0000730007.GTX |
| CH0012032048.GTX |
| DE0005550602.GTX |
| DE0007236101.GTX |
| DE000A1EWWW0.GTX |
| DE000ENAG999.GTX |
| DE000TUAG000.GTX |
| FI0009000681.GTX |
| FR0000120172.GTX |
| FR0000125007.GTX |
| IT0003132476.GTX |
| NL0000235190.GTX |
| NL0011794037.GTX |
| SE0000148884.GTX |

db<>fiddle here