Hi Paul,

thanks for your replies and corrections. I actually put together a version of your example that nests 31 REPLACE functions and then tested it against Jeff's while loop. As it turns out, they are closer performance-wise than you think.

I'm testing on real-life medical data, so I can't share it. But I can tell you that it's varchar(1200) columns containing RTF with very few actual unprintable characters per row. I'm stripping unprintables so I can use a FOR XML query to concatenate RTF fragments into whole documents; unprintable characters are apparently not allowed.

I'm selecting the top 60,000 from a table of 13,000,000 rows and it takes about 30 seconds for the nested REPLACE function to complete, whereas it takes about 40 seconds for Jeff's WHILE loop function.

Interestingly enough, when I select INTO a temp table, either solution completes in almost exactly the same amount of time, or about 26 seconds each.

By selecting into a temp table I can clean the RTF for the full table in about an hour. Is this the best we can do??? Maybe I'm missing a query option or something???

Thanks again,

Carl

Here's my code for your reference:

CREATE FUNCTION dbo.IF_CleanWithReplace

(

@SomeText VARCHAR(MAX)

)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT

cleaned =

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

@SomeText COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

char(0) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(1) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(2) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(3) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(4) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(5) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(6) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(7) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(8) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(9) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(10) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(11) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(12) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(13) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(14) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(15) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(16) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(17) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(18) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(19) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(20) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(21) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(22) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(23) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(24) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(25) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(26) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(27) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(28) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(29) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0)),

char(128) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, SPACE(0));

GO

select top 60000 clean.* from dev.Y_RTF_NOTE_TEXT cross apply dbo.IF_CleanWithReplace(NOTE_RICH_TEXT) as clean