T-SQL中的Regex

正則
T-SQL中的Regex


原作者：Cory Koski
發表時間：2003/06/24


        本文來自Cory Koski。Cory寫道：“我最近遇到一個問題，就是試圖在資料庫域中搜尋一個Regex。還沒有一個版本的SQLServer內部支援Regex，但我發現了一個將Regex的所有優點添加到你的T_SQL應用的方法。為了更容易的使用Regex，我們可以使用自訂函數（User Defined Function, UDF）來協助我們並使工作簡潔。”

在這個解決方案中，我們需要SQL Server 2000或更高。我們還需要確定機器中有VBScript.RegExp類庫，這隨大多數Windows 2000 servers中的Windows Scripting包配有。若你正在使用一個更早版本的Windows，你必須為你的作業系統下載最新版的Windows Scripting。
自訂函數
下面是我的自訂函數，可用來在源字串中搜尋一個正則模式運算式。
CREATE FUNCTION dbo.find_regular_expression
 (
  @source varchar(5000),
  @regexp varchar(1000),
  @ignorecase bit = 0
 )
RETURNS bit
AS
 BEGIN
  DECLARE @hr integer
  DECLARE @objRegExp integer
  DECLARE @objMatches integer
  DECLARE @objMatch integer
  DECLARE @count integer
  DECLARE @results bit
  
  EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END 
  EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
  EXEC @hr = sp_OADestroy @objRegExp
  IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
  END
 RETURN @results
 END

將這個UDF儲存到你的資料庫中，並確定有授權來運行它。當然，你也得確保運行它的人有運行sp_OAxxxxx類擴充預存程序的許可權。
這個函數已確保正常運行，並且即便是和COM對象一起使用，也還是挺快的。

舉例
使用Regex的一個地方就是測試特殊字元。我們不搜尋所有的特殊字元，而是尋找正常字元的匹配項，例如字母和空格。我們看看它的運行。
DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @vchSourceString2 as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- 初始設定變數
SET @vchSourceString = 'Test one This is a test!!'
SET @vchSourceString2 = 'Test two This is a test'

-- 我們的Regex應該類似於
-- [a-zA-Z ]{}
-- 如： [a-zA-Z ]{10}  ...  一個十字元的字串

-- 獲得字串長度
SET @intLength = LEN(@vchSourceString)

-- 設定完整的Regex
SET @vchRegularExpression = '[a-zA-Z ]{' +
CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字元
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(
@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString
IF @bitHasNoSpecialCharacters = 1 BEGIN
 PRINT 'No special characters.'
END ELSE BEGIN
 PRINT 'Special characters found.'
END

PRINT '---'

-- 獲得字串長度
SET @intLength = LEN(@vchSourceString2)

-- 設定完整的Regex
SET @vchRegularExpression = '[a-zA-Z ]{' +
CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字元
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(
@vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2
IF @bitHasNoSpecialCharacters = 1 BEGIN
 PRINT 'No special characters.'
END ELSE BEGIN
 PRINT 'Special characters found.'
END

GO
The results for this example would be:
本例的結果應該是：
Test one This is a test!!
Special characters found.
---
Test two This is a test
No special characters.

結論：
正如你所見，這是一個簡單技巧，在特定的場合得到了非常有用的結果。你作為一個T_SQL開發人員，可以在Regex庫VBScript.RegExp中使用和擴充這個技巧。