sql server正規表現
2920 ワード
最近、sql serverで正規表現を使用して検索置換機能を実装する必要があります.
本来はvisual stuidoのsql server clr機能を使って実現したいと思っていましたが、c#の正則関数を加えることができます.しかし、最終的な導入時にはdeploy failedが常に提示されますが、エラーの原因は分かりません.だからvbaのRegExpで実現するように変更しました.
この機能をsqlのfunctionにしました.コードは以下の通りです.
正規条件を満たす戻り値True、そうでない場合False
RegexReplaceは、正規の置換を実現するために使用されます.
本来はvisual stuidoのsql server clr機能を使って実現したいと思っていましたが、c#の正則関数を加えることができます.しかし、最終的な導入時にはdeploy failedが常に提示されますが、エラーの原因は分かりません.だからvbaのRegExpで実現するように変更しました.
この機能をsqlのfunctionにしました.コードは以下の通りです.
IF OBJECT_ID(N'dbo.VBARegexMatch') IS NOT NULL
DROP FUNCTION dbo.VBARegexMatch
GO
--
CREATE FUNCTION dbo.VBARegexMatch
(
@string VARCHAR(MAX), --
@pattern VARCHAR(255), -- pattern
@IgnoreCase INT = 0 --0 1
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @objRegex INT, @retstr VARCHAR(8000)
--
EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT
--
EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase
EXEC sp_OASetProperty @objRegex, 'Global', 1
--
EXEC sp_OAMethod @objRegex, 'Test', @retstr OUT, @string
--
EXECUTE sp_OADestroy @objRegex
RETURN @retstr
END
GO
EXEC sp_configure 'show advanced options'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ole Automation Procedures',1
RECONFIGURE WITH OVERRIDE
正規条件を満たす戻り値True、そうでない場合False
SELECT dbo.VBARegexMatch('John Smith', '[0-9]', 1) //return False
SELECT dbo.VBARegexMatch('John Smith', '[^0-9]', 1) //return True
IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
DROP FUNCTION dbo.RegexReplace
GO
--
CREATE FUNCTION dbo.RegexReplace
(
@string NVARCHAR(MAX), --
@pattern NVARCHAR(255), --
@replacestr NVARCHAR(255), --
@IgnoreCase INT = 0 --0 1
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @objRegex INT, @retstr NVARCHAR(4000)
--
EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT
--
EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase
EXEC sp_OASetProperty @objRegex, 'Global', 1
--
EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr
--
EXECUTE sp_OADestroy @objRegex
RETURN @retstr
END
GO
RegexReplaceは、正規の置換を実現するために使用されます.
For example:
SELECT dbo.RegexReplace('Firstname Lastname', '([a-z]+)\s([a-z]+)', '$2,$1',1) --reversal first name AND last name
--result:Lastname,Firstname
Select dbo.RegexReplace(N'(1) ??
',N'\]*\>',N'',1)
--Return (1) ??