What's the best approach to embed RegEx in Oracle or SQL Server 2005 SQL?

Question

This is a 3 part question regarding embedded RegEx into SQL statements.

1. How do you embed a RegEx expression into an Oracle PL/SQL select statement that will parse out the “DELINQUENT” string in the text string shown below?

2. What is the performance impact if used within a mission critical business transaction?

3. Since embedding regex into SQL was introduced in Oracle 10g and SQL Server 2005, is it considered a recommended practice?

---

Dear Larry :

Thank you for using ABC's alert service.

ABC has detected a change in the status of one of your products in the state of KS. Please review the information below to determine if this status change was intended.

ENTITY NAME: Oracle Systems, LLC

PREVIOUS STATUS: --

CURRENT STATUS: DELINQUENT

As a reminder, you may contact your the ABC Team for assistance in correcting any delinquencies or, if needed, reinstating the service. Alternatively, if the system does not intend to continue to engage this state, please notify ABC so that we can discontinue our services.

Kind regards,

Service Team 1 ABC

--PLEASE DO NOT REPLY TO THIS EMAIL. IT IS NOT A MONITORED EMAIL ACCOUNT.--

Notice: ABC Corporation cannot independently verify the timeliness, accuracy, or completeness of the public information maintained by the responsible government agency or other sources of data upon which these alerts are based.

Answer 1

Why not just use INSTR (for Oracle) or CHARINDEX (for SQL Server) combined with SUBSTRING? Seems a bit more straightforward (and portable, since it's supported in older versions).

http://www.techonthenet.com/oracle/functions/instr.php and http://www.adp-gmbh.ch/ora/sql/substr.html

http://www.databasejournal.com/features/mssql/article.php/3071531 and http://msdn.microsoft.com/en-us/library/ms187748.aspx

Answer 2

INSTR and CHARINDEX are great alternative approaches but I'd like to explore the benefits of embedding Regex.

Answer 3

In MS SQL you can use LIKE which has some "pattern matching" in it. I would guess Oracle has something similar. Its not Regex, but has some of the matching capabilities. (Note: its not particularly fast).. Fulltext searching could also be an option (again MS SQL) (probably a much faster way in the context of a good sized database)

Answer 4

Why would you need regular expressions here? INSTR and SUBSTR will do the job perfectly.

But if you convinced you need Regex'es you can use:

REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR

(only available in Oracle 10g and up)

SELECT emp_id, text
  FROM employee_comment
 WHERE REGEXP_LIKE(text,'...-....');

Answer 5

If I recall correctly, it is possible to write a UDF in c#/vb for SQL Server. Here's a link, though possibly not the best: http://www.novicksoftware.com/coding-in-sql/Vol3/cis-v3-N13-dot-net-clr-in-sql-server.htm