Replace function and regular expressions

Is it possible to use the REPLACE function in SQL Server 2000 so that
it returns a string containing only alpha-numeric characters (much
like using regular expressions)?

Thank you in advance for any suggestion.

Darren.I think you'll have to do it iteratively:

CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^'
+ @.validchars +']%',@.str), 1) ,'')
RETURN @.str
END

GO

SELECT dbo.CleanChars('TESTING1234','0-9')

--
David Portas
SQL Server MVP
--

Reorganize Index Task - SelectedDatabases expression format

Hi

I am trying to configure the SelectedDatabases property of the Reorganize Index Task using an expression.

The Expressions property of the task provides the ability to configure the SelectedDatabases property of the task using an expression. The properties pane shows that the type of the SelectedDatabases property should be a "(Collection)" (which is edited using the 'Object Collection Editor').

How do I create an expression to configure the SelectedDatabases property? Can I build the collection in text? Or do I need to provide a variable of type System.Object that contains a collection type (and if so exactly what type should it contain)?

TIA . . . Ed

No, you cannot execute an expression successfully for the SelectedDatabases property of the Reorganize Index Task, and to generalize, any "(Collection)" property, and even more generally, any non value-type task property (excepting strings).

The workaround for these types of properties (like the StringCollection

being referred to in this question) is to use the self-modifying

package technique as outlined here:

http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

Or course, one could roll a custom task with an expression-able property of type string, which depending on your familiarity with custom tasks, may be a reasonable choice.

rendering pdf files with expressions in header or footer

I have a quite complex report with a header and footer (displays on every
page) that must be renedered as pdf. In the body I have a table within a
list. This works well and is relatively quick with SP1. However if I add any
expression to a field in the header or footer the pdf file takes 4 minutes
to generate (vs 10s without the expression). For instance, if I add the page
number to the header it will take 4 minutes to generate. I have not
experienced this problem with any other report. Any idea what is causing
this?
ThanksA bit more info on this. I've discovered that it only happens where I have a
table within a list and the grouping on the list is done on a field with a
large number of records. It renders fine in html, but it adds up to 4
minutes for the pdf generation. If I remove the expression from the header
it is fine. If I add a textbox with a value of ABCDEF - no problem. But if I
change that to ="ABCDEF" - very slow pdf generation.
Thanks
"Werner van der Merwe" <wernerv@.nvisionit.co.za> wrote in message
news:egWT8fKcEHA.3988@.tk2msftngp13.phx.gbl...
> I have a quite complex report with a header and footer (displays on every
> page) that must be renedered as pdf. In the body I have a table within a
> list. This works well and is relatively quick with SP1. However if I add
any
> expression to a field in the header or footer the pdf file takes 4 minutes
> to generate (vs 10s without the expression). For instance, if I add the
page
> number to the header it will take 4 minutes to generate. I have not
> experienced this problem with any other report. Any idea what is causing
> this?
>
> Thanks
>