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

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.,jhkjh  khk 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.

Thanks,
Prajesh

-----------------------------------------------------
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

Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 1. Comment: Minor edit

  • Naomi  N edited Original. Comment: Minor grammar corrections, one link

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
Page 1 of 1 (3 items)