Extract Number From String in SQL Server
Extract Number From String in SQL Server: In this tutorial, I am going to create a User Defined Function in SQL Server that will Extract Number From String.
The main logic behind this built-in functions is to replace all the alphanumeric characters with null or empty strings in such a way that at last, we left only with numeric or numbers. So we start the loop from the initial characters and ends when the length of string ends, i.e.
SET @iCount = 0 WHILE @iCount <= LEN(@sInputString)
Here LEN() function returns the total length of input String. And While Loop breaks the Input String into the rows of Single Characters and also replace the alphanumeric characters with empty space or null string and finally Concatenates the broken rows of string into a single string, which now contains only the numeric values or numbers. The detailed Script of extracting a number from String given in this tutorial below.
Sometimes we come to a situation we need to extract Number From String. We can achieve this by Creating a User Defined Function in SQL Server.
You must also see the related Post.
Here is the Script for User Defined Function that will Extract Number From String
Create FUNCTION [dbo].[ExtractNumberFromString](@sInputString VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @iCount INT DECLARE @IntNumbers VARCHAR(1000) SET @iCount = 0 SET @IntNumbers = '' WHILE @iCount <= LEN(@sInputString) BEGIN IF SUBSTRING(@sInputString,@iCount,1) >= '0' AND SUBSTRING(@sInputString,@iCount,1) <= '9' BEGIN SET @IntNumbers = @IntNumbers + SUBSTRING(@sInputString,@iCount,1) END SET @iCount = @iCount + 1 END RETURN @IntNumbers END GO
Run the following Script.To see the output run the below script.
SELECT dbo.ExtractNumberFromString('Here is the Demo of User Defined Function that Will Extract Number From String 1234567')
Here is the output: