How to create/Map base31 "Identity" column in SQL

Question

this is my first time posting to a forum for a question, as I typically prefer to research and find the answer myself, but I am programmer enough to admit database programming is my Achilles' heel.

I am looking to create a base31 Identity Column for use in my database, or from what I'm reading, MAP SQL Identity Column to a Base32 Column.

I am trying to create unique 5 digit alphanumeric sequences in a range from 00000-ZZZZZ. (EX. 0BG85)

I realize that alphanumeric codes aren't ideal as eventually your sequence will spell out many business inappropriate words, thus I will be eliminating vowels (a,e,i,o,u). Hence Base31. The limiting factor with this project is that I am using a Code39 barcode implementation which limits me to 0-9 and A-Z (Capital only).

I have limited exposure to database programming, and my initial thought was to query the last ID generated and then increment the next ID via an algorithm via a C# class. My instincts and everything I've been reading has told me this is an obtuse inelegant way to perform the task.

My research leads me to these few resources

Custom Auto-Generated Sequences with SQL Server

Convert any integer to a string base 2 through 36

I'm wondering if I used the 2nd Link's Function

slightly edited with

Declare @alldigits as varchar(31);
Set @alldigits='0123456789BCDFGHJKLMNPQRSTVWXYZ'

and sent it the Identity column value via a Stored Procedure or Trigger(Never used a trigger before), would this be acceptable? Am I on the right track?

**Answer Found but won't let a me (new user) post my own answer for 5 more hours**

    FUNCTION dbo.CreateBase31ID
    (
@val as BigInt,
@base as int
)
returns varchar(63)
as
Begin
/* From http://sqltips.wordpress.com/2009/01/12/tsql-function-to-convert-decimal-to-hex-octal-or-any-other-base/  */
/* blog text: 
SQL Tips by Namwar Rizvi
  Frequently I see the questions in newsgroups about a function to convert 
  integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). 
  Following TSQL function, which was orginally mentioned by Itzik Ben-Gan 
  in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the 
  ability to convert a given integer into any target base. 
  I have just updated the function with more meaningful names and added some 
  comments to clear the logic.
*/

  /* Check if value is valid and if we get a valid base (2 through 36) */
  If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;

  /* variable to hold final answer */
  Declare @answer as varchar(63);

  /* Following variable contains all 
     possible alpha numeric letters for any valid base 
  */
  Declare @alldigits as varchar(31);
  Set @alldigits='0123456789BCDFGHJKLMNPQRSTVWXYZ'

  /* Set the initial value of 
     final answer as empty string 
  */
  Set @answer='';

  /* Loop while the source value remains greater than 0 */
  While @val>0
  Begin
    Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
    Set @val = @val / @base;
  End

  /* Return the final answer */
  return @answer;
End

This function is working properly when sending an Identity Column value to the function. It maps perfectly to my test values computed by hand. I want to genuinely thank Namwar Rizvi for his original code example and Brian Biales (from the second link in my former post) for explaining and really breaking down Namwar's original function. My bosses think I'm a genius, but in reality I'd be little more than a layman if it weren't for the internet and helpful programmers showing me the way.

I hope this helps someone else down the road.

Answer 1

I know you have a solution but there are a couple of little problems with your script:

• why does it return a VARCHAR(63), what is specific about that size? You will have problems trying to return a VARCHAR(MAX) so I guess you need to know what the largest result would ever be?
• you check for a valid base between 2 and 36, but anything between 32 and 36 would run out of conversion digits;
• you only ever really want to convert to base 31 so why parameterise the base?

I reformatted it a little and got this:

FUNCTION BigIntToBase31Ish (
    @Value BIGINT)
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @Result VARCHAR(255) = '';
    DECLARE @Base INT = 31;
    DECLARE @ConvertDigits VARCHAR(31) = '0123456789BCDFGHJKLMNPQRSTVWXYZ';

    --Is the integer value valid?
    IF @Value < 0
        RETURN NULL;

    --Convert the integer value to base 31
    WHILE @Value > 0
    BEGIN
        SELECT @Result = SUBSTRING(@ConvertDigits,@Value % @Base + 1, 1) + @Result;
        SELECT @Value = @Value / @Base;
    END;
    RETURN @Result;
END;