Oracle PL/SQL REGEXP_LIKE / REGEXP_INSTR

Question

Hi,

I require a means of checking to see if a string has the following exact pattern within it, i.e.:

(P)

Examples where this would be true is:

'Test System (P)'

Unsure though how to check for cases when the string that doesn't have '(P)', i.e:

'Test System (GUI for Prof)' - in this case, this would be false but I am using REGEXP_LIKE and it actually returns TRUE.

I only want it to return True when the exact string of '(P)' exists within the search string.

Any help achieving this using PL/SQL would be great.

Thanks.

Answer 1

Use:

REGEX_LIKE(t.column, '\(P\)')

Regular-Expressions.info is a great resource.

Regular INSTR would work (Oracle 8i+):

WHERE INSTR(t.column, '(P)') > 0 --column contains '(P)'

WHERE INSTR(t.column, '(P)') = 0 --column does NOT contain '(P)'

LIKE works too:

WHERE t.column LIKE '%(P)%' --column contains '(P)'

WHERE t.column NOT LIKE '%(P)%' --column does NOT contain '(P)'

Answer 2

Try like:

WHERE thing like '%(P)%';