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

Introduction

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
  1. CREATE FUNCTION dbo.GetNumericValue  
  2. (@strAlphaNumeric VARCHAR(256))  
  3. RETURNS VARCHAR(256)  
  4. AS  
  5. BEGIN  
  6. DECLARE @intAlpha INT  
  7. SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)  
  8. BEGIN  
  9. WHILE @intAlpha > 0  
  10. BEGIN  
  11. SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )  
  12. SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )  
  13. END  
  14. END  
  15. RETURN ISNULL(@strAlphaNumeric,0)  
  16. END  
  17. 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
  1. SELECT dbo.GetNumericValue(''AS 'Empty';  
  2. SELECT dbo.GetNumericValue('HSP14569AS79RR5'AS 'Alpha Numeric';  
  3. SELECT dbo.GetNumericValue('14569'AS 'Numeric';  
  4. SELECT dbo.GetNumericValue('HSP'AS 'String';  
  5. SELECT dbo.GetNumericValue(NULLAS '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.