Advanced Oracle SQL Development

Advanced

SQL

We use the latest Oracle SQL versions that dramatically extend the SQL language

features allowing the language to do sophisticated things with a non-procedural

approach:


Regular Expression Support

enables developers to implement complex match logic in the database using four build-in functions REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR and REGEXP_SUBSTR in SQL expressions. For example:

REGEXP_LIKE (code,'^[[:digit:]]{3,3}$') expression can be used to check that the code column contains three and only three digits.

REGEXP_REPLACE (data_str,' {2,}',' ') expression removes unnecessary spaces in the data_str column value.

REGEXP_INSTR(address,'[^[:digit:]|'' '']') function returns position number for the first non-numeric character and not a space as well.

REGEXP_SUBSTR (long_str, '(\S*)(\s)', 1, 2) function returns the second word in the string stored in the long_str column.

The select statement returns departments records where department name has three or more words:

SELECT * FROM departments

WHERE REGEXP_INSTR(department_name, '[^ ]+', 1, 3) != 0

SQL regular expressions give powerful capabilities to data consistency support through the CHECK constraints. For example:

The emp_email_ck constraint set a rule for the email address that can contain the mailbox name not more than 32 characters starting with a letter and domain 32 + 6 length strings with the dot delimiter/

ALTER TABLE employees ADD CONSTRAINT emp_email_ck CHECK

 (REGEXP_LIKE (email,'^([a-z])([a-z0-9_\.-]{1,31})@([\da-z\.-]{1,32})\.([a-z\.]{2,6})$'))