SQL codeThe OLE Regex Find (Execute) function-----------------------------This is the most powerful function for doing complex finding and replacing of text. As it passes back detailed records of the hits, including the location and the backreferences, it allows for complex manipulations.*/IF OBJECT_ID(N'dbo.RegexFind') IS NOT NULL DROP FUNCTION dbo.RegexFindGOcreate function RegexFind( @pattern VARCHAR(255), @matchstring VARCHAR(8000), @global BIT = 1, @Multiline bit =1)returns @result TABLE ( Match_ID INT, FirstIndex INT , length INT , Value VARCHAR(2000), Submatch_ID INT, SubmatchValue VARCHAR(2000), Error Varchar(255) )AS -- columns returned by the function begin DECLARE @objRegexExp INT, @objErrorObject INT, @objMatch INT, @objSubMatches INT, @strErrorMessage VARCHAR(255), @error varchar(255), @Substituted VARCHAR(8000), @hr INT, @matchcount INT, @SubmatchCount INT, @ii INT, @jj INT, @FirstIndex INT, @length INT, @Value VARCHAR(2000), @SubmatchValue VARCHAR(2000), @objSubmatchValue INT, @command VARCHAR(8000), @Match_ID INT DECLARE @match TABLE ( Match_ID INT IDENTITY(1, 1) NOT NULL, FirstIndex INT NOT NULL, length INT NOT NULL, Value VARCHAR(2000) ) DECLARE @Submatch TABLE ( Submatch_ID INT IDENTITY(1, 1), match_ID INT NOT NULL, SubmatchNo INT NOT NULL, SubmatchValue VARCHAR(2000) ) SELECT @strErrorMessage = 'creating a regex object',@error='' EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT IF @hr = 0 SELECT @strErrorMessage = 'Setting the Regex pattern', @objErrorObject = @objRegexExp IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern IF @hr = 0 SELECT @strErrorMessage = 'Specifying a case-insensitive match' IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1 IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline IF @hr = 0 EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global IF @hr = 0 SELECT @strErrorMessage = 'Doing a match' IF @hr = 0 EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @matchstring IF @hr = 0 SELECT @strErrorMessage = 'Getting the number of matches' IF @hr = 0 EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT SELECT @ii = 0 WHILE @hr = 0 AND @ii < @Matchcount BEGIN/*The Match object has four read-only properties. The FirstIndex property indicates the number of characters in the string to the left of the match. The Length property of the Match object indicates the number of characters in the match. The Value property returns the text that was matched.*/ SELECT @strErrorMessage = 'Getting the FirstIndex property', @command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex' IF @hr = 0 EXEC @hr= sp_OAGetProperty @objmatch, @command, @Firstindex OUT IF @hr = 0 SELECT @strErrorMessage = 'Getting the length property', @command = 'item(' + CAST(@ii AS VARCHAR) + ').Length' IF @hr = 0 EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT IF @hr = 0 SELECT @strErrorMessage = 'Getting the value property', @command = 'item(' + CAST(@ii AS VARCHAR) + ').Value' IF @hr = 0 EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT INSERT INTO @match ( Firstindex, [Length], [Value] ) SELECT @firstindex + 1, @Length, @Value SELECT @Match_ID = @@Identity /*The SubMatches property of the Match object is a collection of strings. It will only hold values if your regular expression has capturing groups. The collection will hold one string for each capturing group. The Count property indicates the number of string in the collection. The Item property takes an index parameter, and returns the text matched by the capturing group. The Item property is the default member, so you can write SubMatches(7) as a shorthand to SubMatches.Item(7). Unfortunately, VBScript does not offer a way to retrieve the match position and length of capturing groups.*/ IF @hr = 0 SELECT @strErrorMessage = 'Getting the SubMatches collection', @command = 'item(' + CAST(@ii AS VARCHAR) + ').SubMatches' IF @hr = 0 EXEC @hr= sp_OAGetProperty @objmatch, @command, @objSubmatches OUT IF @hr = 0 SELECT @strErrorMessage = 'Getting the number of submatches' IF @hr = 0 EXEC @hr= sp_OAGetProperty @objSubmatches, 'count', @submatchCount OUT SELECT @jj = 0 WHILE @hr = 0 AND @jj < @submatchCount BEGIN IF @hr = 0 SELECT @strErrorMessage = 'Getting the submatch value property', @command = 'item(' + CAST(@jj AS VARCHAR) + ')' ,@submatchValue=null IF @hr = 0 EXEC @hr= sp_OAGetProperty @objSubmatches, @command, @SubmatchValue OUT INSERT INTO @Submatch ( Match_ID, SubmatchNo, SubmatchValue ) SELECT @Match_ID, @jj+1, @SubmatchValue SELECT @jj = @jj + 1 END SELECT @ii = @ii + 1 END IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @Error = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '') END EXEC sp_OADestroy @objRegexExp EXEC sp_OADestroy @objMatch EXEC sp_OADestroy @objSubMatchesinsert into @result (Match_ID, FirstIndex, [length], [Value], Submatch_ID, SubmatchValue, error) SELECT m.[Match_ID], [FirstIndex], [length], [Value],[SubmatchNo], [SubmatchValue],@error FROM @match m LEFT OUTER JOIN @submatch s ON m.match_ID=s.match_ID if @@rowcount=0 and len(@error)>0insert into @result(error) select @error return endGO--showing the context where two words 'for' and 'last' are found in proximityDeclare @sample varchar(2000)Select @Sample='You have failed me for the last time, Admiral. We have not long to wait for your last gasp'Select '...'+substring(@Sample,Firstindex-8,length+16)+'...' from dbo.RegexFind ('\bfor(?:\W+\w+){0,3}?\W+last\b', @sample,1,1)--finding repeated words, showing the repetition and the repeated word Select [repetition]=value, [word]=SubmatchValue from dbo.RegexFind ('\b(\w+)\s+\1\b','this this is is a repeated word word word',1,1)--Split lines based on a regular expressionSelect value from dbo.regexfind('[^\r\n]*(?:[\r\n]*)','This is the second lineThis is the thirdand the fourth',1,1) where length>0--break up all words in a string into separate table rowsselect value from dbo.RegexFind ('\b[\w]+\b','Hickory dickory dock, the mouse ran up the clock',1,1)--split text into keywords and valuesselect Match_ID, [keyword]=max (case when submatch_ID=1 then submatchValue else '' end),[value]=max (case when submatch_ID=2 then submatchValue else '' end) from dbo.RegexFind ('(\w+)\s*=\s*(.*)\s*','firstname=PhilLastname=FactorSalary=$200,000age=unknown to usPost=DBA',1,1) group by Match_IDselect * from dbo.RegexFind ('([^\|\r\n]+[\|\r\n]+)','1|white gloves|2435|245652|Sports Shoes|285678|09873|Stumps|2845|9874|bat|29862|4875',1,1)--get valid dates and convert to SQL Server format Select distinct convert(datetime,value,103) from dbo.RegexFind ('\b(0?[1-9]|[12][0-9]|3[01])[- /.](0?[1-9]|1[012])[- /.](19|20?[0-9]{2})\b','12/2/2006 12:30 <> 13/2/200732/3/20072-4-200725.8.20071/1/200534/2/21042/5/2006',1,1)