Jeff,

I have used your script both in UDF and as a query. But the problem is when the table records exceeds 10000, there is a performance issue. It takes more than two mins.

This is my table:

DECLARE @intCount INT

SET @intCount = 10000

WHILE(@intCount>=1)

BEGIN

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

SET @intCount = @intCount - 1

END

Also, i have tried out a function that i have created that has loop

CREATE FUNCTION dbo.ufn_ExtractNumbersFromText

(

@vchInput VARCHAR(200)

)

RETURNS VARCHAR(200)

AS

BEGIN

DECLARE @vchOutput VARCHAR(200)

SET @vchInput = LTRIM(RTRIM(@vchInput))

DECLARE @i INT

DECLARE @intCount INT

DECLARE @vchTemp VARCHAR(1)

SET @i = 1

SET @vchOutput = ''

SET @intCount = LEN(@vchInput)

WHILE(@intCount >= 1)

BEGIN

SET @vchTemp = SUBSTRING(@vchInput,@i,1)

SET @vchOutput= @vchOutput + CASE WHEN @vchTemp LIKE '[0-9]'

THEN @vchTemp ELSE '' END

SET @i = @i + 1

SET @intCount = @intCount - 1

END

RETURN @vchOutput

END

And this takes nearly 6 Secs For 20000.

Anyways the script you have sent can be modified or are there any other hidden advantage compared to my function.