RegExp.Execute
-
Hello,
What is the correct syntax for a call to the RegExp.Execute method from sp_OAMethod?
The following is not working:
Exec @HR = sp_OAMethod @RegEx, 'Execute', @InStr, @Tokens Output
Some example VBScript code (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsmthexecute.asp):
Set regEx = New RegExp ' Create a regular expression.
regEx.Pattern = patrn ' Set pattern.
regEx.IgnoreCase = True ' Set case insensitivity.
regEx.Global = True ' Set global applicability.
Set Matches = regEx.Execute(strng) ' Execute search.
For Each Match in Matches ' Iterate Matches collection.
RetStr = RetStr & "Match found at position "
RetStr = RetStr & Match.FirstIndex & ". Match Value is '"
RetStr = RetStr & Match.Value & "'." & vbCRLF
Next
I've got all the calls to sp_OACreate and sp_OASetProperty working properly.
Let me know if you would like to see my code so far.
Richard
-
If you could modify for Northwind and post it, I'll take a look.
-
Hi Steve,
I'm trying to get a result set consisting of the letters of the alphabet. It's contrived; Once I get this function working, it will actually be used to parse multiple expressions out of one varchar column.
Any luck, let me know!!!
Thanks a ton.
Richard
---
If Exists(Select * From SysObjects Where Name = 'fnRegex' And Type = 'TF')
Drop Function fnRegex
go
Create Function fnRegex
(
@InStr Varchar(8000)
, @InRegex Varchar(8000)
)
Returns
@Items Table (
ItemId Int Not Null Identity
, Item Varchar(300) Not Null
)
As
Begin
Declare
@RegEx Int
, @HR Int
, @Tokens Varchar(2000)
Insert Into @Items (Item) Values ('Hello1')
-- Exec @HR = sp_OACreate 'VBScript.RegExp', @RegEx Output
Exec @HR = sp_OACreate 'RegExp', @RegEx Output
If @HR <> 0
Begin
Insert Into @Items (Item) Values ('Hello2')
Exec @HR = sp_OASetProperty @RegEx, 'Pattern', '[A-Z] *'
If @HR <> 0
Begin
Insert Into @Items (Item) Values ('Hello3')
Exec @HR = sp_OAMethod @RegEx, 'Execute', @InStr, @Tokens Output
If @HR <> 0
Begin
Insert Into @Items (Item) Values (IsNull(@Tokens, 'Hello4'))
End
End
Exec sp_OADestroy @RegEx
End
Return
End
go
Select * From fnRegEx('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z', '[A-Z] *')
-
Here you go! (Straight from the horse's mouth)
* Noel -
Hi,
That's interesting code that Ken has written, and it does help me, but it doesn't answer my original question; He's using the test method, I would like to use the execute method.
I'm trying to parse out multiple expressions from one column, of one row.
For example:
Create Table RRTest (C1 Varchar(100) Not Null)
Insert Into RRTest (C1) Values ('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z ')
I'd like to be able to run: Select * From fnRegEx(RRTest.C1, '([A-Z] )*') and get a result set back of 26 rows.
I think I'm going to have to burn the midnight oil alone on this one!
Richard
-
Just use 'item("x").value' where x represents the result item to get (from 0 to count-1) to get the result value
sp_OAMethod doesn't like
exec @intResult=sp_OAMethod @intRegExp,'item("'+convert(varchar(10),@intResultItem)+'").value',@chrResult OUT
so you will need to use
declare @chrTemp varchar(100)
set @chrTemp=convert(varchar(10),@intResultItem)+'").value'
exec @intResult=sp_OAMethod @intRegExp,@chrTemp,@chrResult OUT
Here's a simple example to get the number of urls and the text of the first url in a text field:
create function udf_GetRegExp
()
returns int
as
begin
declare @intRegExp int,@intResult int
exec @intResult=sp_OACreate 'VBScript.RegExp',@intRegExp OUT
return @intRegExp
end
create function udf_getUrls
( @intRegExp int,@chrWhichResult varchar(20),@txtInput text )
returns varchar(100)
as
begin
declare @intResult int,@intMatches int,@intResultCount int,@return varchar(100)
exec @intResult=sp_OASetProperty @intRegExp,[global],true
if @intResult=0
BEGIN
exec @intResult=sp_OASetProperty @intRegExp,[ignorecase],true
if @intResult=0
BEGIN
exec @intResult=sp_OASetProperty @intRegExp,[pattern],'\b(http://)?(\S*\.)+[a-zA-Z]{2,3}(/\S*)*\b'
if @intResult=0
BEGIN
EXEC @intResult=sp_OAMethod @intRegExp,[Execute],@intMatches OUT,@txtInput
if @intResult=0
BEGIN
if @chrWhichResult='count'
BEGIN
EXEC @intResult=sp_OAGetProperty @intMatches,'count',@intResultCount OUT
if @intResult=0
set @return=convert(varchar(100),@intResultCount)
else
set @return='Error getting count'
END
else
BEGIN
EXEC @intResult=sp_OAGetProperty @intMatches,@chrWhichResult,@return OUT
if @intResult0
set @return='Error getting result property'
END
END
else
set @return='Error getting result object'
END
else
set @return='Error setting pattern'
END
else
set @return='Error setting ignoreCase'
END
else
set @return='Error setting global'
return @return
end
create table #test( textfield text )
insert #test(textfield)values( 'the url dottywood.org/artinthepark will take you to my website')
insert #test(textfield)values( 'this line does not contain a url')
declare @intRegExp int,@intResult int
set @intRegExp=dbo.udf_GetRegExp()
if @intRegExp=null
print 'Error creating regexp object'
else
select dbo.udf_GetURLS(@intRegExp,'count',textfield) as count,dbo.udf_GetURLS(@intRegExp,'item(0).value',textfield) as url from #test
EXEC @intResult=sp_OADestroy @intRegExp
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply