Can I do Regular Expressions using TSQL in SQL Server2005?

I am running PHP with SQL Server 2005. I have [bold] in some of the values in my result set, which needs to be striped out. I had a function in SQL Server2000 that did that, but the same function does not work in SQL Server2005. Any dieas?

CREATE FUNCTION

dbo.fn_regex(@.pattern varchar(255), @.matchstring varchar(8000))

RETURNS int

AS

BEGIN

declare @.obj int

declare @.res int

declare @.match bit

set @.match=0

exec @.res=sp_OACreate 'VBScript.RegExp',@.obj OUT

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OASetProperty @.obj, 'Pattern', @.pattern

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OASetProperty @.obj, 'IgnoreCase', 1

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OAMethod @.obj, 'Test',@.match OUT, @.matchstring

IF (@.res <> 0) BEGIN

RETURN NULL

END

exec @.res=sp_OADestroy @.obj

return @.match

END

GO

When you say the functiuon doesn't work in SQL 2005; in what way doesn't it work? I see that you are using sp_OAxxx and friends; you are aware that you need to explicitly enable sp_OAxxx in S2K5, as a server setting?

Also, youwould be much, much, much better off using the CLR integration to do your stuff than sp_OA

Niels
|||Thanks for your response nielsb. Do you know if I can use CLR integration in a TSQL function. I am using PHP with SQL Server, so I do not have the option to use VB.net or C#?

Thanks!|||I don't fully understand the question? But, you can have s function written in C#, VB.NET, that's doing your stuff, and that function can be called by some T-SQL functions.

Niels
|||I cannot write the function in VB.NET or C#, as I am using PHP with SQL Server 2005 (which leave me the only option of write a TSQL function)|||Hmm, I still don't understand why you can not write a VB.NET or C# function. I.e. SQL Server 2005 hosts the CLR. You write a CLR function doing your regex stuff, this function is hosted by SQL Server and exposed by a T-SQL function. Your PHP code then calls the T-SQL function. Or am I totally off-track here?

Niels
|||Thanks for the response Niels! Do you think I can write a C# or VB.NET CLR function and call it within TSQL? Could you maybe give me an example of a CLR function. I have not used this before. your response is very much appreciated!