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 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 a string concatenation.

Implementation
In my case I want to generate 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 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 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.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.