Hey Jeff,

Scalar T-SQL functions?! WHILE loops? I am genuinely shocked! :w00t:

The REPLACE solution can be made to run an order of magnitude faster if we use an in-line table-valued function instead of an evil scalar function:

Create the in-line table-valued function

CREATE FUNCTION dbo.IF_CleanWithReplace

(

@SomeText VARCHAR(100)

)

RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT cleaned =

REPLACE(

REPLACE(

REPLACE(

@SomeText COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS,

'A', SPACE(0)),

'E', SPACE(0)),

'-', SPACE(0));

Run the test

DECLARE @Bitbucket VARCHAR(8000);

SELECT @Bitbucket = iTVF.cleaned

FROM #Dirty D

CROSS

APPLY dbo.IF_CleanWithReplace(D.SomeText) iTVF;

Execution times (scalar function times in parentheses):

#Dirty: 501ms (13,234ms)

Paul

edit: code updated in-place to reflect Carl's excellent observation that the previous implementation was fast, but failed to return the correct results :hehe: