Wow.... you're right. I've never seen the XML concatenation method run so slow. Now that we have the test data you want, I believe we can do a little better than the While Loop solution you have.

Here's your test data generator (I did a little "Mo-dee-can tweekin' on it) and your test table (best I can make out from your code)...

--===== Create the test table and populate it

CREATE TABLE #Temp (Data VARCHAR(100), OutPut BIGINT)

INSERT INTO #Temp (Data)

SELECT TOP 20000 '~`!@#$%^&*()_2+-={}[]:";<>,.?/|\",.~`'

FROM Master.sys.SysColumns sc1

CROSS JOIN Master.sys.SysColumns sc2

Here's my idea of what the function should look like....

CREATE FUNCTION dbo.DigitsOnly

(@SomeString VARCHAR(8000))

RETURNS BIGINT

AS

BEGIN

DECLARE @CleanString VARCHAR(8000)

SET @CleanString = ''

SELECT @CleanString = @CleanString + SUBSTRING(@SomeString,t.N,1)

FROM dbo.Tally t

WHERE t.N <= LEN(@SomeString)

AND SUBSTRING(@SomeString,t.N,1) LIKE '[0-9]'

RETURN CAST(@CleanString AS BIGINT)

END

Notice that it uses a Tally table... the following article explains not only how to build one, but also how it replaces a While Loop... it's got a heck of a lot more uses than just what show up in that article...

http://www.sqlservercentral.com/articles/TSQL/62867/

... And, here's the code to use the function...

SELECT * FROM #Temp

UPDATE #Temp SET Output = dbo.DigitsOnly(Data)

SELECT * FROM #Temp

Including the two selects, the While loop version takes between 9 and 10 seconds on my box. The Tally table version takes between 4 and 5.

Here's another test table you can run... same everything except different data...

drop table #Temp

go

--===== Create the test table and populate it

CREATE TABLE #Temp (Data VARCHAR(100), OutPut BIGINT)

INSERT INTO #Temp (Data)

SELECT TOP 20000 '~`!@#$%^&*()_2+-={}[]:";<>,.?/|\",.~`'+RIGHT(NEWID(),12)

FROM Master.sys.SysColumns sc1

CROSS JOIN Master.sys.SysColumns sc2

Both runs take about the same time as the prevously did with the Tally table version being almost twice as fast.

Hope all that helps.