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)
I am not sure 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.
How can I do this in PL/SQL?
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)'
Try like:
WHERE thing like '%(P)%';
I would stick with REGEXP_* functions, as you'll need to practice them anyway, and knowing regular expressions will serve you well.
They're all good answers, except for a typo in Ponies' first answer. :
The typo is that there's a P missing from REGEX_LIKE:
Written: REGEX_LIKE(t.column, '\(P\)')
Correct: REGEXP_LIKE(T.COLUMN, '\(P\)')
The '\' is an escape character that says "don't look for the symbolic meaning of the next character, but look for the literal character itself."
