Commits

adamv  committed 814a8d2

Adding alternate RegExp support via VBScript.RegExp object.

  • Participants
  • Parent commits 1066743

Comments (0)

Files changed (2)

File sqlserver/regex_com/DEPLOY.txt

+This script adds Regex support to SQL Server via the VBScript.RegExp object.
+To use this object, OLE Automation needs to be enabled on the server, as 
+described here:
+    http://msdn2.microsoft.com/en-us/library/ms191188.aspx
+
+with these SQL statements:
+
+sp_configure 'show advanced options', 1;
+GO
+RECONFIGURE;
+GO
+sp_configure 'Ole Automation Procedures', 1;
+GO
+RECONFIGURE;
+GO

File sqlserver/regex_com/regexp_like.sql

+CREATE FUNCTION dbo.REGEXP_LIKE
+(
+	@input nvarchar(4000),
+	@pattern nvarchar(4000),
+	@caseSensitive int
+)
+RETURNS bit
+AS
+BEGIN
+	DECLARE @hresult integer
+	DECLARE @oRegexp integer
+	DECLARE @objMatches integer
+	DECLARE @objMatch integer
+	DECLARE @count integer
+	DECLARE @results bit
+	
+	EXEC @hresult = sp_OACreate 'VBScript.RegExp', @oRegexp OUTPUT
+	IF @hresult <> 0 return -1;
+
+	EXEC @hresult = sp_OASetProperty @oRegexp, 'Pattern', @pattern
+	IF @hresult <> 0 return -1;
+
+	EXEC @hresult = sp_OASetProperty @oRegexp, 'Global', false
+	IF @hresult <> 0 return -1;
+
+	IF @caseSensitive = 0 EXEC @hresult = sp_OASetProperty @oRegexp, 'IgnoreCase', 1
+	IF @hresult <> 0 return -1;
+
+	EXEC @hresult = sp_OAMethod @oRegexp, 'Test', @results OUTPUT, @input
+	IF @hresult <> 0 return -1;
+
+	EXEC @hresult = sp_OADestroy @oRegexp
+	IF @hresult <> 0 return -1;
+
+	RETURN @results
+END