Site Loader
Get a Quote

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.

Introduction:

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.

Extract Number From String in SQL Server

You must also see the related Post.

How to Split String with a delimiter in SQL Server.

Functions SQL Server.

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:

Extract Number From String in SQL Server

Hope This small post about How to Extract Number From String in SQL Server will help you.

Thank You

Post Author: TechieFlair

Leave a Reply

Your email address will not be published.