How to split Dynamics GP's alphanumeric column values in SQL Server

I decided to go back to my roots! Yes, my blog started with SQL Server tricks around Dynamics GP and slowly (but surely!) progressed into other interesting technical topics, so I decided to scout my bag of tricks and found two useful user-defined function scripts that I regularly use on most of my SQL Server developement projects around GP.

Today, I am featuring two scalar-valued functions that allow you to separate numeric values from character values in alphanumeric fields such as purchase order numbers, receipt numbers, audit trail codes, among others. This can be particularly useful when developing integrating SQL Server applications that need to grab the next document number and increment the numeric portion. While I have seen many solutions out there, most of them use some sort of loop structure to get to evaluate each alphanumeric character in the string sequence to determine whether it's a numeric value or an alpha value. Any loop structure, as insignificant as it may seem, always takes a toll on the way your application or script performs.

The following two scalar-valued functions are only compatible with SQL Server 2005 and above, and make use of the constant values associated to certain SQL Server bit options stored in the master.dbo.spt_values table to validate whether a character is a number or an alpha. To make this script more international, you can decide to setup your own table of numbers (or characters) and modify the script to assert each character in the string against your table. Let's take a look at the two functions:

First, the function script to retrieve the alpha portion of the string.

dbo.fGetAlpha

create function dbo.fGetAlpha(@string nvarchar(100))
returns nvarchar(100)
begin
  return
  ( select cast(
      ( select case
          when substring(@string, n, 1) like '[^0-9]' then
             substring(@string, n, 1)
          else ''
          end
        from ( select number from master..spt_values
               where type = 'P' and number between 1 and 100) AS Nums(n)
        where n <= LEN(@string) for XML path('')) AS nvarchar(100)       )   );     end  GO

With a minor tweak to the previous script -- note the use of '[0-9]' -- we have a script that can retrieve the numeric portion of the string.

dbo.fGetNumber

create function dbo.fGetNumber(@string nvarchar(100))
returns nvarchar(100)
begin
  return
  ( select cast(
      ( select case
          when substring(@string, n, 1) like '[0-9]' then
             substring(@string, n, 1)
          else ''
          end
        from ( select number from master..spt_values
               where type = 'P' and number between 1 and 100) AS Nums(n)
        where n <= LEN(@string) for XML path('')) AS nvarchar(100)       )    );     end  GO

One thing to highlight is the LIKE clause suppors RegEx-type ranges like [0-9], [^0-9], [a-z], [A-Z] or anything you may be able to think of in terms of ranges.

Practical Use Case

Say for example you want to separate the value UPR0000010102 into it's alpha and numeric portions, this is UPR and 0000010102. You can use the functions as follows to accomplish this:

Sample Use Code

declare @uprTransactionNumber char(30), @uprAlpha char(30), @uprNumber char(30)

set @uprTransactionNumber = 'UPR0000010102'
select @uprAlpha = dbo.fGetAlpha(@uprTransactionNumber), @uprNumber = dbo.fGetNumber(@uprTransactionNumber)

When the script is executed, the numeric portion of the string will be stored in the @uprNumber variable and the alpha portion of the string will be stored in the @uprAlpha variable.

Downloads

Click here to download the SQL script file with the create function statements.

Until next post

MG.-
Mariano Gomez, MIS, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/