Someone asked, what is the fastest way to remove all non-numeric characters (including space) from a varchar variable without affecting the performance. This will be used to check multiple columns in the table so thread originator was looking for some efficient way to do this:
Example of the input:
12 fgfgf.,jhkjh khk 56789jhjh67
Desired output:
125678967
CREATE
FUNCTION
fnGetNumericOnly (@string
VARCHAR
(500))
RETURNS
(500)
AS
BEGIN
DECLARE
@NumericOnlyPart
(500) =
''
;
@
Numeric
(1) =
@start
INT
= 1;
end
= 1
SELECT
= LEN(@string);
WHILE (@start <= @
)
SET
=
SUBSTRING
(@string, @start, @start + 1)
IF ASCII(@
) >= 48
AND
ASCII(@
) <= 57
@NumericOnlyPart = @NumericOnlyPart + @
END
@start = @start + 1;
RETURN
GO
--Test Basic
dbo.fnGetNumericOnly(
'12 fgfgf.,jhkjh khk 56789jhjh67'
--Test on Million Records
TABLE
TestFunctionPerf (Data
(8000))
TRUNCATE
TestFunctionPerf
= 100000
-- 100,000 Records
INSERT
INTO
VALUES
(
@start = @start + 1
Data
FROM
--13 sec
dbo.fnGetNumericOnly(Data)
-- 8 sec
Naomi N edited Revision 1. Comment: Minor edit
Naomi N edited Original. Comment: Minor grammar corrections, one link
The solution here in blogs.lessthandot.com/.../extracting-numbers-with-sql-server just get the starting numerics and not all the nos in the text