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!

Can a UDF be forced into being considered deterministic?

I have a UDF which uses regular expressions vicariously through a
VBScript.RegExp COM object, and does many replaces with it (which
could probably be optimized to a single regexp, but is much more
readable as-is).
This function operates 100% deterministically, but is extremely slow.
The type of queries it is used for are along the lines of
SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
GROUP BY GETBASEMODEL(ModelNumber)
As any information these regular expressions remove from our item
codes has absolutely nothing to do with its weight (i.e. color).
My end goal is to speed up the above query, as it currently takes
about an hour to run when given < 100k rows.
My thought was to create a indexed computed column in the
CompletedItems table: "BaseModel", with a function of
GETBASEMODEL(ModelNumber), though because my function uses the EXEC
command, it is inherently non-deterministic.
Is there a keyword that I can use to force my function to be
deterministic? Or (even better) is my mindset completely wrong?
The functions appear below
---
CREATE FUNCTION dbo.GETBASEMODEL
(
@.model varchar(15)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @.ret varchar(255)
DECLARE @.RegexObject integer
SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
SET @.ret = @.model
--trim trailing spaces
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
--remove trailing dash and color code
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
--remove common trailing color codes in case of no dash
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
--remove jetting code
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
--remove trailing "/S" or "/F" or "/E"
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
--BF## -> BF
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
--BFMD## -> BF
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
--LBF/RBF -> BF
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
--TSL/TSR -> TS(L or R)
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
R)')
--trailing M
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
--handing for specific models
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
or 0L)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
or 2R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
or 4R)')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
or 4R)')
SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
RETURN @.ret
END
CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
(
)
RETURNS integer
AS
BEGIN
DECLARE @.hr integer
DECLARE @.objRegExp integer
DECLARE @.ret integer
-- create regex object
EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
SET @.ret = @.objRegExp
RETURN @.ret
END
CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
(
@.objRegExp integer
)
RETURNS integer
AS
BEGIN
DECLARE @.hr integer
--destroy regex object
EXEC @.hr = sp_OADestroy @.objRegExp
RETURN @.hr
END
CREATE FUNCTION dbo.REGEX_REPLACE
(
@.objRegExp integer,
@.original varchar(255),
@.pattern varchar(255),
@.replace_with varchar(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @.hr integer
DECLARE @.ret varchar(255)
EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
@.replace_with
IF @.hr <> 0 BEGIN
SET @.ret = NULL
RETURN @.ret
END
RETURN @.ret
ENDAdd a new column to your table that will contain the result of the
GetBaseModel function. From a quick look at your function, the ModelNumber
seem to be a very complex object; so it's quite possible that for having the
best performance of your database; you might have to create more than one
new column in order to capture all the features of this object.
For your deterministic problem, you must always create your UDF with the
WITH SCHEMABINDING option if you want to use it with a computed column in a
View; even when the function is already clearly deterministic. Maybe this
will help in your case.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Walbert" <wkalata@.gmail.com> wrote in message
news:1187902579.443728.320620@.q4g2000prc.googlegroups.com...
>I have a UDF which uses regular expressions vicariously through a
> VBScript.RegExp COM object, and does many replaces with it (which
> could probably be optimized to a single regexp, but is much more
> readable as-is).
> This function operates 100% deterministically, but is extremely slow.
> The type of queries it is used for are along the lines of
> SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
> GROUP BY GETBASEMODEL(ModelNumber)
> As any information these regular expressions remove from our item
> codes has absolutely nothing to do with its weight (i.e. color).
> My end goal is to speed up the above query, as it currently takes
> about an hour to run when given < 100k rows.
> My thought was to create a indexed computed column in the
> CompletedItems table: "BaseModel", with a function of
> GETBASEMODEL(ModelNumber), though because my function uses the EXEC
> command, it is inherently non-deterministic.
> Is there a keyword that I can use to force my function to be
> deterministic? Or (even better) is my mindset completely wrong?
> The functions appear below
> ---
> CREATE FUNCTION dbo.GETBASEMODEL
> (
> @.model varchar(15)
> )
> RETURNS VARCHAR(255)
> AS
> BEGIN
> DECLARE @.ret varchar(255)
> DECLARE @.RegexObject integer
> SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
> SET @.ret = @.model
> --trim trailing spaces
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
> --remove trailing dash and color code
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
> --remove common trailing color codes in case of no dash
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
> --remove jetting code
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
> --remove trailing "/S" or "/F" or "/E"
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
> --BF## -> BF
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
> --BFMD## -> BF
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
> --LBF/RBF -> BF
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
> --TSL/TSR -> TS(L or R)
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
> R)')
> --trailing M
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
>
> --handing for specific models
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
> or 0L)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
> or 2R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
> or 4R)')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
> SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
> or 4R)')
> SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
> RETURN @.ret
> END
>
> CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
> (
> )
> RETURNS integer
> AS
> BEGIN
> DECLARE @.hr integer
> DECLARE @.objRegExp integer
> DECLARE @.ret integer
> -- create regex object
> EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> SET @.ret = @.objRegExp
> RETURN @.ret
> END
>
>
> CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
> (
> @.objRegExp integer
> )
> RETURNS integer
> AS
> BEGIN
> DECLARE @.hr integer
> --destroy regex object
> EXEC @.hr = sp_OADestroy @.objRegExp
> RETURN @.hr
> END
>
>
> CREATE FUNCTION dbo.REGEX_REPLACE
> (
> @.objRegExp integer,
> @.original varchar(255),
> @.pattern varchar(255),
> @.replace_with varchar(255)
> )
> RETURNS VARCHAR(255)
> AS
> BEGIN
> DECLARE @.hr integer
> DECLARE @.ret varchar(255)
> EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
> @.replace_with
> IF @.hr <> 0 BEGIN
> SET @.ret = NULL
> RETURN @.ret
> END
> RETURN @.ret
> END
>|||On Aug 23, 9:38 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> Add a new column to your table that will contain the result of the
> GetBaseModel function. From a quick look at your function, the ModelNumber
> seem to be a very complex object; so it's quite possible that for having the
> best performance of your database; you might have to create more than one
> new column in order to capture all the features of this object.
> For your deterministic problem, you must always create your UDF with the
> WITH SCHEMABINDING option if you want to use it with a computed column in a
> View; even when the function is already clearly deterministic. Maybe this
> will help in your case.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Walbert" <wkal...@.gmail.com> wrote in message
> news:1187902579.443728.320620@.q4g2000prc.googlegroups.com...
> >I have a UDF which uses regular expressions vicariously through a
> > VBScript.RegExp COM object, and does many replaces with it (which
> > could probably be optimized to a single regexp, but is much more
> > readable as-is).
> > This function operates 100% deterministically, but is extremely slow.
> > The type of queries it is used for are along the lines of
> > SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
> > GROUP BY GETBASEMODEL(ModelNumber)
> > As any information these regular expressions remove from our item
> > codes has absolutely nothing to do with its weight (i.e. color).
> > My end goal is to speed up the above query, as it currently takes
> > about an hour to run when given < 100k rows.
> > My thought was to create a indexed computed column in the
> > CompletedItems table: "BaseModel", with a function of
> > GETBASEMODEL(ModelNumber), though because my function uses the EXEC
> > command, it is inherently non-deterministic.
> > Is there a keyword that I can use to force my function to be
> > deterministic? Or (even better) is my mindset completely wrong?
> > The functions appear below
> > ---
> > CREATE FUNCTION dbo.GETBASEMODEL
> > (
> > @.model varchar(15)
> > )
> > RETURNS VARCHAR(255)
> > AS
> > BEGIN
> > DECLARE @.ret varchar(255)
> > DECLARE @.RegexObject integer
> > SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
> > SET @.ret = @.model
> > --trim trailing spaces
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
> > --remove trailing dash and color code
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
> > --remove common trailing color codes in case of no dash
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
> > --remove jetting code
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
> > --remove trailing "/S" or "/F" or "/E"
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
> > --BF## -> BF
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
> > --BFMD## -> BF
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
> > --LBF/RBF -> BF
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
> > --TSL/TSR -> TS(L or R)
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
> > R)')
> > --trailing M
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
> > --handing for specific models
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
> > or 0L)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
> > or 2R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
> > or 4R)')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
> > or 4R)')
> > SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
> > RETURN @.ret
> > END
> > CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
> > (
> > )
> > RETURNS integer
> > AS
> > BEGIN
> > DECLARE @.hr integer
> > DECLARE @.objRegExp integer
> > DECLARE @.ret integer
> > -- create regex object
> > EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > SET @.ret = @.objRegExp
> > RETURN @.ret
> > END
> > CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
> > (
> > @.objRegExp integer
> > )
> > RETURNS integer
> > AS
> > BEGIN
> > DECLARE @.hr integer
> > --destroy regex object
> > EXEC @.hr = sp_OADestroy @.objRegExp
> > RETURN @.hr
> > END
> > CREATE FUNCTION dbo.REGEX_REPLACE
> > (
> > @.objRegExp integer,
> > @.original varchar(255),
> > @.pattern varchar(255),
> > @.replace_with varchar(255)
> > )
> > RETURNS VARCHAR(255)
> > AS
> > BEGIN
> > DECLARE @.hr integer
> > DECLARE @.ret varchar(255)
> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
> > @.replace_with
> > IF @.hr <> 0 BEGIN
> > SET @.ret = NULL
> > RETURN @.ret
> > END
> > RETURN @.ret
> > END
Well, Model Numbers aren't very complex as much as they've evolved
over the last thirty years without anyone ever dreaming they'd have to
be dealt with pragmatically. A "RE7903L/6-WH" would be a "Residential
Tub/Shower, model "7903", colored white, with six jets and the
shower's wall on the left side. At the time it hits the scale,
though, it's only a white model "7903", which is what the
GETBASEMODEL() function tries to reduce it to. (oh, and we make bath
tubs)
Two problems:
any UDF which uses the EXECUTE function is ineligable to be used with
WITH SCHEMABINDING
and
Performance without being able to index the computed column is just as
bas as using GETBASEMODEL() in a query.
I was thinking a good solution would be a non-computed column, with a
default value of GETBASEMODEL(ModelNumber), and hopefully being able
to work security around not being able to overwrite its value. Does
this seem viable or prone to error?|||> I was thinking a good solution would be a non-computed column, with a
> default value of GETBASEMODEL(ModelNumber), and hopefully being able
> to work security around not being able to overwrite its value. Does
> this seem viable or prone to error?
Another option is to maintain the persisted value via an INSERT/UPDATE
trigger. This will ensure the value is kept in sync as long as the
trigger(s) fires.
FWIW, the underlying issue is that it's best to store atomic data in a
relational database. However, I understand that this isn't always feasible
for legacy applications.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Walbert" <wkalata@.gmail.com> wrote in message
news:1187923624.672726.6270@.z24g2000prh.googlegroups.com...
> On Aug 23, 9:38 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>> Add a new column to your table that will contain the result of the
>> GetBaseModel function. From a quick look at your function, the
>> ModelNumber
>> seem to be a very complex object; so it's quite possible that for having
>> the
>> best performance of your database; you might have to create more than one
>> new column in order to capture all the features of this object.
>> For your deterministic problem, you must always create your UDF with the
>> WITH SCHEMABINDING option if you want to use it with a computed column in
>> a
>> View; even when the function is already clearly deterministic. Maybe
>> this
>> will help in your case.
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>> "Walbert" <wkal...@.gmail.com> wrote in message
>> news:1187902579.443728.320620@.q4g2000prc.googlegroups.com...
>> >I have a UDF which uses regular expressions vicariously through a
>> > VBScript.RegExp COM object, and does many replaces with it (which
>> > could probably be optimized to a single regexp, but is much more
>> > readable as-is).
>> > This function operates 100% deterministically, but is extremely slow.
>> > The type of queries it is used for are along the lines of
>> > SELECT MIN(GETBASEMODEL(ModelNumber)), AVG(Weight) FROM CompletedItems
>> > GROUP BY GETBASEMODEL(ModelNumber)
>> > As any information these regular expressions remove from our item
>> > codes has absolutely nothing to do with its weight (i.e. color).
>> > My end goal is to speed up the above query, as it currently takes
>> > about an hour to run when given < 100k rows.
>> > My thought was to create a indexed computed column in the
>> > CompletedItems table: "BaseModel", with a function of
>> > GETBASEMODEL(ModelNumber), though because my function uses the EXEC
>> > command, it is inherently non-deterministic.
>> > Is there a keyword that I can use to force my function to be
>> > deterministic? Or (even better) is my mindset completely wrong?
>> > The functions appear below
>> > ---
>> > CREATE FUNCTION dbo.GETBASEMODEL
>> > (
>> > @.model varchar(15)
>> > )
>> > RETURNS VARCHAR(255)
>> > AS
>> > BEGIN
>> > DECLARE @.ret varchar(255)
>> > DECLARE @.RegexObject integer
>> > SET @.RegexObject = dbo.REGEX_CREATE_OBJECT()
>> > SET @.ret = @.model
>> > --trim trailing spaces
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'\s+$','')
>> > --remove trailing dash and color code
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'-..$','')
>> > --remove common trailing color codes in case of no dash
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'(WH|BO|BC|SS)$','')
>> > --remove jetting code
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/\d*[LRC]{0,2}$','')
>> > --remove trailing "/S" or "/F" or "/E"
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'/[FES]$','')
>> > --BF## -> BF
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BF\d+','BF')
>> > --BFMD## -> BF
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'BFMD\d*','BF')
>> > --LBF/RBF -> BF
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'[LR]BF','BF')
>> > --TSL/TSR -> TS(L or R)
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'TS[LR]','TS(L or
>> > R)')
>> > --trailing M
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'M$','')
>> > --handing for specific models
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3632[LR]','3632')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3636[LR]','3636')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660[LR]','3660')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3660F[LR]','3660F')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3672F[LR]','3672F')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3717[LR]','3717')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3748[LR]','3748')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3837[LR]','3837')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3939[LR]','3939')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'3940[LR]','3940')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4232[LR]','4232')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4236[LR]','4236')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4436[LR]','4436')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'440[12][LR]','440(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'460[12][LR]','460(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4630[LR]','4630')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4830[LR]','4830')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4835[LR]','4835')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4836[LR]','4836')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'4837[LR]','4837')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5000[LR]','5000')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5435[LR]','5435')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5836[LR]','5836')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5843[LR]','5843')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'5845[LR]','5845')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'600[01][LR]','600(1R
>> > or 0L)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6032[LR]','6032')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6033[LR]','6033')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6036[LR]','6036')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6042[LR]','6042')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6048[LR]','6048')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6133[LR]','6133')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6333[LR]','6333')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'6843[LR]','6843')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7236[LR]','7236')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'740[34][LR]','740(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7532[LR]','7532')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7900[LR]','7900')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'790[34][LR]','790(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'791[12][LR]','791(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'793[34][LR]','793(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'7936[LR]','7936')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8000[LR]','8000')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'801[12][LR]','801(1L
>> > or 2R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'840[34][LR]','840(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'862[34][LR]','862(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'863[34][LR]','863(3L
>> > or 4R)')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8802[LR]','8802')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848D[LR]','8848D')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8848[LR]','8848')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'8864[LR]','8864')
>> > SET @.ret = dbo.REGEX_REPLACE(@.RegexObject, @.ret,'962[34][LR]','962(3L
>> > or 4R)')
>> > SET @.RegexObject = dbo.REGEX_DESTROY_OBJECT(@.RegexObject)
>> > RETURN @.ret
>> > END
>> > CREATE FUNCTION dbo.REGEX_CREATE_OBJECT
>> > (
>> > )
>> > RETURNS integer
>> > AS
>> > BEGIN
>> > DECLARE @.hr integer
>> > DECLARE @.objRegExp integer
>> > DECLARE @.ret integer
>> > -- create regex object
>> > EXEC @.hr = sp_OACreate 'VBScript.RegExp', @.objRegExp OUTPUT
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Global', false
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'IgnoreCase', true
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > SET @.ret = @.objRegExp
>> > RETURN @.ret
>> > END
>> > CREATE FUNCTION dbo.REGEX_DESTROY_OBJECT
>> > (
>> > @.objRegExp integer
>> > )
>> > RETURNS integer
>> > AS
>> > BEGIN
>> > DECLARE @.hr integer
>> > --destroy regex object
>> > EXEC @.hr = sp_OADestroy @.objRegExp
>> > RETURN @.hr
>> > END
>> > CREATE FUNCTION dbo.REGEX_REPLACE
>> > (
>> > @.objRegExp integer,
>> > @.original varchar(255),
>> > @.pattern varchar(255),
>> > @.replace_with varchar(255)
>> > )
>> > RETURNS VARCHAR(255)
>> > AS
>> > BEGIN
>> > DECLARE @.hr integer
>> > DECLARE @.ret varchar(255)
>> > EXEC @.hr = sp_OASetProperty @.objRegExp, 'Pattern', @.pattern
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > EXEC @.hr = sp_OAMethod @.objRegExp, 'Replace', @.ret OUTPUT, @.original,
>> > @.replace_with
>> > IF @.hr <> 0 BEGIN
>> > SET @.ret = NULL
>> > RETURN @.ret
>> > END
>> > RETURN @.ret
>> > END
>
> Well, Model Numbers aren't very complex as much as they've evolved
> over the last thirty years without anyone ever dreaming they'd have to
> be dealt with pragmatically. A "RE7903L/6-WH" would be a "Residential
> Tub/Shower, model "7903", colored white, with six jets and the
> shower's wall on the left side. At the time it hits the scale,
> though, it's only a white model "7903", which is what the
> GETBASEMODEL() function tries to reduce it to. (oh, and we make bath
> tubs)
> Two problems:
> any UDF which uses the EXECUTE function is ineligable to be used with
> WITH SCHEMABINDING
> and
> Performance without being able to index the computed column is just as
> bas as using GETBASEMODEL() in a query.
> I was thinking a good solution would be a non-computed column, with a
> default value of GETBASEMODEL(ModelNumber), and hopefully being able
> to work security around not being able to overwrite its value. Does
> this seem viable or prone to error?
>|||> I was thinking a good solution would be a non-computed column, with a
> default value of GETBASEMODEL(ModelNumber), and hopefully being able
> to work security around not being able to overwrite its value. Does
> this seem viable or prone to error?
This was grosso modo my suggestion when I was talking about adding a new
column; with the possibility of adding other columns for the possibility of
other functions beside GetBaseModel(). Don't forget that much of the work
that SQL-Server is doing with a materialized view is something like that: it
computes all the values for the column but instead of storing it in the
table, it store it in a hidden location.
Of course, having your own column is not exactly the same thing because you
must take into account a few things like the insertion or edition of the
column. However, in most cases, this shouldn't be a big problem.
Another possibility - and maybe the best one - would be to get rid of all
these regular expression manipulations and doing your own string
manipulation character by character in a UDF. Your use of regular
expressions seems to be overkill to me for what you are doing. Not only you
will be able to use a materialized view but your code will perform many
orders of magnitude faster.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Walbert" <wkalata@.gmail.com> wrote in message
news:1187923624.672726.6270@.z24g2000prh.googlegroups.com...

Calling subreports conditionally?

Hi,
ist it possible to call subreports conditionally? Applying expressions on
the visibility property leads to some blank spaces, especially within PDFs,
so it would be better, not to call the subreport at all, if not wanted...
ThanksHave you already tried using the expression on the "Visibility" property of
the ROW(not the property for the subreport itself)? The subreport will still
be called but you can skip the display of the row.
"Niklas" wrote:
> Hi,
> ist it possible to call subreports conditionally? Applying expressions on
> the visibility property leads to some blank spaces, especially within PDFs,
> so it would be better, not to call the subreport at all, if not wanted...
> Thanks