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

How to identify slash asterisk using C# and Regex

This is very very annoyin. It took me a lot of time to figure out how to identify /* and */.
The code bellow do the following: it find the occurrences of /* and */

==============================================================
Method 1
        //Get all left slashs with asterisk.
         public static ArrayList getAllMatches(string pText)
        {
            ArrayList arrMatches = new ArrayList();
            string stringToTest = pText;
            const string patternToMatch = @"/\*";
            Regex regex = new Regex(patternToMatch, RegexOptions.Compiled);
            MatchCollection matches = regex.Matches(stringToTest);
            foreach (Match match in matches)
            {
                arrMatches.Add(match.Index);
            }
            return arrMatches;
      }

=================================================================
Method 2
        //Get all right slashs with asterisk.
         public static ArrayList getAllMatches(string pText)
        {
            ArrayList arrMatches = new ArrayList();
            string stringToTest = pText;
            const string patternToMatch = @"\*/";
            Regex regex = new Regex(patternToMatch, RegexOptions.Compiled);
            MatchCollection matches = regex.Matches(stringToTest);
            foreach (Match match in matches)
            {
                arrMatches.Add(match.Index);
            }
            return arrMatches;
      }

I hope it is useful for you folks.