Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by
  • When: 26 Jul 2013 9:04 AM
  • Last revision by (eMicrosoft Partne)
  • When: 26 Jul 2013 9:19 AM
  • Revisions: 3
  • Comments: 3
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  T-SQL: Removing all non-Numeric Characters from a String

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

, , [Edit tags]
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
  • 26 Jul 2013 9:19 AM

    Naomi  N edited Revision 1. Comment: Minor edit

  • 26 Jul 2013 9:18 AM

    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)