Share via

T-SQL: Removing all non-Numeric Characters from a String

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.,jhkjhkhk 56789jhjh67

Desired output:

125678967

So here is the solution I wrote. Create a scalar function that takes the string with alpha numeric and special characters and I want to get only numbers from it. I don't want the special character or alphabets.

CREATE FUNCTION  fnGetNumericOnly (@string VARCHAR(500))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @NumericOnlyPart VARCHAR(500) = '';
DECLARE @Numeric VARCHAR(1) = ''
DECLARE @start INT = 1;
DECLARE @end INT  = 1
 
SELECT @end = LEN(@string);
 
WHILE (@start <= @end)
BEGIN
SET @Numeric = SUBSTRING(@string, @start, @start + 1)
 
IF ASCII(@Numeric) >= 48
AND  ASCII(@Numeric) <= 57
BEGIN
SET @NumericOnlyPart = @NumericOnlyPart + @Numeric;
END
 
SET @start = @start + 1;
END
 
RETURN @NumericOnlyPart
END
GO
 
--Test Basic
SELECT dbo.fnGetNumericOnly('12 fgfgf.,jhkjh  khk 56789jhjh67')
GO
 
--Test on Million Records
CREATE TABLE  TestFunctionPerf (Data VARCHAR(8000))
GO
 
TRUNCATE TABLE  TestFunctionPerf
GO
 
DECLARE @start INT = 1;
DECLARE @end       INT = 100000 -- 100,000 Records
 
WHILE (@start <= @end)
BEGIN
INSERT INTO  TestFunctionPerf
VALUES ('12 fgfgf.,jhkjh  khk 56789jhjh67')
 
SET @start = @start + 1
END
GO
 
SELECT Data
FROM TestFunctionPerf --13 sec
 
SELECT dbo.fnGetNumericOnly(Data) AS Data
FROM TestFunctionPerf -- 8 sec

Any comments to improve awaited.

Resources and more information

  • The solution above is very straightforward, but this is not a best performing solution as we loop through the string. This problem is well known in the internet, I will only give one link where this problem has a different better performing solution
    Extracting numbers with SQL Server

See Also