USING REGEX WITH SQLSERVER

STEP 1

sp_configure 'show advanced options', 1

GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;

STEP 2

Create this function

USE [Nike]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexCodigoProduto]    Script Date: 04/02/2015 15:17:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[RegexCodigoProduto] (@CodigoProduto VARCHAR(8000), @RegexPattern VARCHAR(4000)) RETURNS VARCHAR(20)
AS
BEGIN
Declare @subject varchar(8000) = @CodigoProduto
Declare @pattern varchar(4000) = @RegexPattern --Pattern
DECLARE @objRegexExp INT, @objMatch INT, @Result VARCHAR(8000)

--Creating COM object
EXEC sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
--Assigning Properties to COM object
EXEC sp_OASetProperty @objRegexExp, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
EXEC sp_OASetProperty @objRegexExp, 'MultiLine', 1
EXEC sp_OASetProperty @objRegexExp, 'Global', false
EXEC sp_OASetProperty @objRegexExp, 'CultureInvariant', true

--Executing the COM object
EXEC sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @subject
--Fetching the first matching value
EXEC sp_OAGetProperty @objmatch, 'item(0).Value' , @Result OUT
--Releasing COM object after use
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objRegexExp

RETURN @Result

END