Using Regular Expressions

Question

Using Regular Expressions

She Through Whom All Data Flows

Ten Centuries

Points: 1209
More actions
September 28, 2009 at 1:56 pm

#211445

Can somebody explain to me how to activate functionality for using regular expressions in SQL server 2005?
This article looked hopeful
http://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/
Although I don't really understand what it's doing, I thought I'd try running this code and hopefully it would create the function RegExMatch for me, which is all I want. It did create a function, but when I try to run it I get "execute permission denied on object sp_OASetProperty".
I have also looked at this:
http://www.sqlservercentral.com/Forums/Topic650459-145-1.aspx
and
http://www.sqlservercentral.com/Forums/Topic490684-149-1.aspx?Highlight=regex
But this is over my head: I don't know any C#, have no idea what "CLR" is or what it means to "enable CLR functionality in the surface area configuration tool". Can somebody dumb this down for me???
I am familiar with using regular expressions in MS Access, and am just trying to duplicate this functionality in SQL server. HEre's the MS Access VB code I've been using:
Function ValidString(StringToCheck As Variant, PatternToUse As String, Optional CaseSensitive As Boolean = False)
Dim reCurr As Object
On Error GoTo Err_ValidString
If Len(StringToCheck & vbNullString) > 0 Then
Set reCurr = CreateObject("vbscript.regexp")
reCurr.Pattern = PatternToUse
reCurr.ignorecase = Not CaseSensitive
ValidString = reCurr.test(StringToCheck)
Else
ValidString = False
End If
Set reCurr = Nothing
Exit Function
Err_ValidString:
Select Case Err.Number
Case 429
MsgBoxAutoClose "Could not use regular expressions; please notify Vickie Chapman! ", "ValidString function error", 30000
Case Else
MsgBoxAutoClose "ValidString function error; please notify Vickie Chapman!" & vbCrLf & _
"Error # " & Err.Number & " :" & Err.Description, "Error", 30000
End Select
End Function
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

The code on Simple-Talk uses OLE automation. The error you're getting is that you aren't set up to do that on the server you're trying it on.

Do you have administrative rights on the server? If so, you can give yourself execution rights on the necessary stored procedures for that.

For the rest of it, I recommend looking up "surface area configuration", and "clr" (also called "common language runtime") online. Start by Googling/Binging/whatever, those terms.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

OTF SSCertifiable Points: 6104 More actions · Answer 2

Most DBA's won't be keen on allowing you use the sp_OA* set of procedures (Ole automation) on a

Production Server.

The better approach, if you are using Sql Server 2005 is to use a CLR Object.

In order to do this, however, you will need to have some understanding of coding in C#/VB.Net or any other

language that supports the .Net Framework. You will also need to have a basic understanding of the implementation

of Regular Expressions in .Net.

In any case, the steps to implement your requirements would go something like this:

1. Code the database object (Stored Procedure/Function) in the .net language and Editor of your choice.

2. Compile the code, noting the location of the resulting DLL.

3. Ensure CLR is enabled on the Server using the sp_Configure System Procedure.

4. Import the Assembly into Sql Server using syntax similar to this:

create assembly assemblyName

from 'your dll'

with permission_set=[whatever is appropriate: can be safe, external or unsafe];

5. Create the Procedure/s within the database of your choice with syntax similar to this:

create procedure procedureName

as

External_Name [assemblyName].[className].[methodName];

Hope that helps you in some way.

She Through Whom All Data Flows Ten Centuries Points: 1209 More actions · Answer 3

Thanks. At this point this is enough to convince me that I don't need regular expressions that badly.

[font="Comic Sans MS"]She Through Whom All Data Flows[/font]