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.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin