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

Answer 2

This pattern:

'.+d\$'

is really strings. It is looking for at least one character (non-newline generally) followed by the lowercase 'd' and then by the literal '$'. So, this matches:

Xd$
123d$abc

It is equivalent to: like '%_d$%'.

In all likelihood, you intend the much more reasonable '\d+$'. This would match any string that ends in a digit.