Tuesday, July 17, 2007

Using Regular Expressions in T-SQL

Well, you can! But you need to use the sp_OA* stored procedures and VBScript. Here is a link to the user defined function that allows you to regex in sql:
Regular Expressions in T-SQL

If you check out the comments to this link you can even find a Regex search and replace solution.

You may get this error while trying to use the OLE Automation :
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

The solution I've found is use sp_configure to enable Ole Automation Procedures like this:

EXEC sp_configure 'show advanced options', 1 -- make them available

EXEC sp_configure 'Ole Automation Procedures', 1 -- turn on OLE

EXEC sp_configure -- to see the new value
EXEC sp_configure 'show advanced options', 0 -- make them unavailable