Split Numbers From Alphanumeric String In SQL Server

watch_later 29 January, 2021


This article gives an explanation about how to split numbers from the alphanumeric string in an SQL server. Here I'll also explain how to create a function in an SQL server.

In my previous articles, I explained Dynamic PIVOT Query and Concatenate value from multiple rows to a single string in SQL Server and How to Find Values Within a Given Range in SQL server and How to Find the Last Date of Any Month in SQL Server and Split Comma Separated String Using STRING_SPLIT in SQL Server as well as Fixed Error 26 - Error Locating Server/Instance Specified in SQL Server 2008 that you might like to read.

How To Split Numbers From Alphanumeric String In SQL Server
While we working with any data-driven applications, sometimes we need to split the numbers and alphabets from the input string as per the given requirement. I got many emails from the students and beginner programmers to write an article on ways to get an only integer value from the string in SQL server. So today in this article I'll explain how to archive this requirement to split the numbers and alphabets and return only integer value from the string.


1) What is an alphanumeric string?

2) How to get integer numbers from an alphanumeric string in SQL Server?


What is an alphanumeric string?

The alphanumeric string is a string. It is a combination of alphabets from a-z and A-Z and integer numbers 0 to 9.

Get Numbers from string

Let's split the alphanumeric string and get only integer numbers from the string. So, we will take an example for demonstration. 

I have my enrollment number, which is a combination of numbers and alphabets, and I want only integer numbers from my enrollment number.


Input (Enrollment Number): SOE14CE13017

Expected Output: 1413017

SQL Query to Get Numbers From String

DECLARE @strEnrollmentNumber NVARCHAR(MAX) = 'SOE14CE13017'
DECLARE @intNumber INT  
SET @intNumber = PATINDEX('%[^0-9]%', @strEnrollmentNumber)  
WHILE @intNumber > 0  
	SET @strEnrollmentNumber = STUFF(@strEnrollmentNumber, @intNumber, 1, '' )  
	SET @intNumber = PATINDEX('%[^0-9]%', @strEnrollmentNumber )  


As you can see in the query above, here, we have declared two different temp variables @strEnrollmentNumber which indicates an Input string, and @intNumber that is taken to check whether the input string contains a number or not. Then using the PATINDEX function of the SQL server we have identified that the string input string contains a number or not and stored the return value of this function into @intNumber

In SQL server PATINDEX is a function that accepts search pattern and expression(input string) as a parameter and returns, starting position of the first occurrence of the pattern in a specified expression(input string), PATINDEX will return 0 if the pattern is not found in the specified expression(input string). Here, we have used a pattern '%[^0-9]%' that indicates only numbers from 0 to 9.

Now, by using the while loop in the SQL server we removed the characters from the input string which not match with the given pattern '%[^0-9]%' one by one using the STUFF function and store the result in the @strEnrollmentNumber variable and again set the value of @intNumber as per the specified pattern '%[^0-9]%' as we used condition @intNumber > 0  in while loop, So it will do the same process again and again and remove alphabets from the input string one by one till @intNumber gets 0 and remove all the alphabets from the input string.

In SQL Server STUFF() function is used to deletes a specified sequence of characters from a source/Input string and then inserts another set of sequence of characters at a specified starting point. I have written an article on STUFF() function with syntax and examples that you might like to read.

Use of Query

SELECT @strEnrollmentNumber



You also can create a function to get only numbers from the input string to reduce the complexity of the query.

Function to Get Numbers From String

CREATE FUNCTION dbo.GetNumbersFromString
	@strInputString  VARCHAR(256)
	DECLARE @intValue INT  
	SET @intValue = PATINDEX('%[^0-9]%', @strInputString)  
		WHILE @intValue > 0  
			SET @strInputString = STUFF(@strInputString, @intValue, 1, '' )  
			SET @intValue = PATINDEX('%[^0-9]%', @strInputString )  
RETURN ISNULL(@strInputString,0)  

Use of Function

SELECT dbo.GetNumbersFromString('SOE14CE13017')




As you can see in the created function above here we have created a function that accepts strInputString as an argument and all the logic is the same as I explained in the above SQL query to return numbers from the string. Finally, this function returns the integer value from the string and If the input string dose does not contain any numbers then this function will return 0.


In this article, we learned how to split the numbers and alphabets from the alphanumeric string in the SQL server as well as about the PATINDEX() and STUFF() function of the SQL server and way create a function in SQL server that returns an integer value.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

sentiment_satisfied Emoticon