How To Split/Separate Numbers And Alphabets From Alpha Numeric String In SQL Server

Today I am going to Explain how you can split/separate numbers and alphabets from an alphanumeric string in SQL server. When you work with any database related application either that is in web or windows application sometime based on your requirement you have alphanumeric string and you just want only numbers from that string and want to use that numbers in your entire application as per your need like either use like as a variable, parameter or also use like as a string concatenation.

Introduction

Today, I am going to explain how you can split/separate numbers and alphabets from an alphanumeric string in an SQL server.

When you work with any database-related application, either in Web or Windows applications, sometimes based on your requirement you have an alphanumeric string and you only want numbers from that string and want to use those numbers in your entire application as per your need, possibly as a variable, parameter, or string concatenation.

 

I got many emails and request to write an article on split only alphabets from the alphanumeric string and return only varchar value using SQL. I have already written this article, so you can visit this my article "How to Split Alphabets from Alphanumeric String In SQL Server". So you can read that article to archive your requirement. 

Implementation

In my case, I want to generate the auto-increment token number and that token number will generate with a combination of My Invoice Number and Heder Name of Store, and in my Invoice Table Invoice Number like "HSP14569" where "HSP" is the Header Name of Store. That can change based on Store selection and "14569" is my Invoice Number.

Actually, what I need is to split my invoice number from "HSP14569" To "14569" and increment with "1," so that will be "14570". Now, I will contact this new number with my header of the store.

So, yesterday I wrote one user-defined function in the SQL server, which will return only numeric values from my string.

SQL Server User-Defined Function

CREATE FUNCTION dbo.GetNumericValue
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Note
You can modify this user defined function based on your need.

Let's see how you can use this user-defined function. Below, I have included some of the ways to use this function.

 

SQL Server Select Statment

SELECT dbo.GetNumericValue('') AS 'Empty';
SELECT dbo.GetNumericValue('HSP14569AS79RR5') AS 'Alpha Numeric';
SELECT dbo.GetNumericValue('14569') AS 'Numeric';
SELECT dbo.GetNumericValue('HSP') AS 'String';
SELECT dbo.GetNumericValue(NULL) AS 'NULL';

Output

Summary

You can see the result was generated as above. If you have some alternate way to achieve this kind of requirement then please let me know, or if you have some query then please leave your comments.