![]() IF REGEXP_LIKE('subject', 'regexp') THEN /* Match */ ELSE /* No match */ END IF ĪLTER TABLE mytable ADD (CONSTRAINT mycolumn_regexp CHECK (REGEXP_LIKE(mycolumn, '^regexp$'))) SELECT * FROM mytable WHERE REGEXP_LIKE(mycolumn, 'regexp', 'i') The regexp parameter is a string with your regular expression. The source parameter is the string or column the regex should be matched against. You can also use it in a CHECK constraint. In a PL/SQL script, it returns a Boolean value. You can use it in the WHERE and HAVING clauses of a SELECT statement. REGEXP_LIKE(source, regexp, modes) is probably the one you’ll use most. You can use these equally in your SQL and PL/SQL statements. Oracle Database 10g offers four regular expression functions. Oracle 11g and 12c use the same regex flavor as 10g R2. Oracle 10g R2 further extends the regex syntax by adding a free-spacing mode (without support for comments), shorthand character classes, lazy quantifiers, and the anchors \A, \Z, and \z. Oracle’s regex engine will interpret the string '\t' as the regex t when passed as the regexp parameter. In SQL statements, you’ll need to type an actual tab or line break in the string with your regular expression to make it match a tab or line break. You can use these with a POSIX engine in a programming language like C , because the C compiler will interpret the \t and \n in string constants. If you’ve worked with regular expressions in other programming languages, be aware that POSIX does not support non-printable character escapes like \t for a tab or \n for a newline. It is a traditional NFA engine, like all non-POSIX regex flavors discussed on this website. As I explained in the topic about the POSIX standard, it requires the regex engine to return the longest match in case of alternation. It won’t cause any errors, but may result in different matches. ![]() Obviously, if you only work with Oracle, these differences are irrelevant. The result is that all POSIX ERE regular expressions can be used with Oracle, but some regular expressions that work in Oracle may cause an error in a fully POSIX-compliant engine. Oracle allows this, and simply ignores the backslash. The POSIX standard states it is illegal to escape a character that is not a metacharacter with a backslash. In a fully compliant engine, \1 through \9 would be illegal. The POSIX ERE standard does not support these, even though POSIX BRE does. First, Oracle supports the backreferences \1 through \9 in the regular expression. Oracle does not implement the POSIX ERE standard exactly, however. The NLS_SORT setting determines the POSIX locale used, which determines the available collating sequences and equivalence classes. Oracle fully supports collating sequences and equivalence classes in bracket expressions. These functions implement the POSIX Extended Regular Expressions (ERE) standard. ![]() With version 10g Release 1, Oracle Database offers 4 regexp functions that you can use in SQL and PL/SQL statements. ![]()
0 Comments
Leave a Reply. |