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 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.


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))  
  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  
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';  


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.