SQL-Server T-SQL UDF: Encode / Decode Decimal Numbers to/from ANY Number Base (Hexadecimal, Binary, Alpha, etc)

SQL Server Source Code for UDF (User Defined Function) to Convert From/To (Between) Decimal, Hexadecimal, Binary, Alphanumeric or ANY Number Base

Below, I have provided the source code for two Transact-SQL User Defined Functions that work together to convert (i.e., encode / decode) between base-10 (i.e., Decimal or radix-10) and any other radix, whether hexadecimal or binary or some random alphanumeric or symbol-based encoding.

Why would you want to convert from decimal radix-10 number to another number-base and back? Here are a few potential reasons and use-case examples:

• Generate hexadecimal equivalent for decimal values; 
• Generate binary equivalent for decimal values; 
• Generate base-26, base-52, and base-N equivalent for decimal values — this function is quite useful for generating much smaller (less total storage-characters) alphanumeric-equivalents of a base-10 counterpart (perhaps for uses like a customer-number, invoice-number, transaction-code, etc); 
• the bottom line is that you can just as easily use this procedure to to convert base-10 to binary string, hexadecimal, or your own special alphanumeric and/or special-character-infused "numbers" or "codes" for many different situations.

With that in mind, the two routines are as follows, and are counterparts to each other, making it rather simple to interchange between much smaller (less total characters) alphanumeric-equivalents of a base-10 counterpart:

1. Microsoft SQL-Server Transact-SQL (T-SQL) user-defined function (UDF) that converts a base-10 (i.e., Decimal or radix-10) number (@iNumber parm) into a number of base @iNewBase (i.e., the same value but represented in a numeral system with a different radix / root).
2. Next is the UDF that converts a number currently represented in any numeral system with a different base / radix / root (i.e., base-N number) to a base-10 / radix-10 (i.e., Decimal) number.

Note: remember that in order to perform simple arithmetic like addition (increment) and subtraction (decrement) operations using normal mathematical operators available to you in SQL, you will first want to cast your base-N number back to decimal, perform the operation, and then convert it back to your chosen base-N representation.

SQL-Server User Defined Function (UDF) Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************


--********************************************************************************
-- Convert a base-10 (i.e., Decimal) number (@Base10Number parm) and into base-@Radix number.  
-- This is quite useful for generating much smaller (less total display characters)
-- alphanumeric-equivalents of a base-10 counterpart.
-- Commonly used to convert base-10 to binary string, hexadecimal, alphanumeric, etc.
-- Easily modified with additional parameters to fit your needs.
-- This function has a counterpart, udfConvertAnyBaseNumberToBase10, used to reverse the op.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1 (NULL):     ' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(NULL, 2, NULL)  IS NULL
--THEN 'SUCCESS' ELSE 'FAIL' END;
--
--SELECT 'TEST2 (BINARY):   ' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(10, 2, NULL)  = '1010'
--THEN 'SUCCESS' ELSE 'FAIL' END; 
--
--SELECT 'TEST3 (HEX):      ' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(1000, 16, NULL) = '3E8'
--THEN 'SUCCESS' ELSE 'FAIL' END;
--
--SELECT 'TEST4 (ALPHA26):  ' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(512, 26, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = 'TS' 
--THEN 'SUCCESS' ELSE 'FAIL' END;
--
--SELECT 'TEST5 (ALPHANUM): ' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(1000, 36, NULL) = 'RS'
--THEN 'SUCCESS' ELSE 'FAIL' END;
--
--SELECT 'TEST6 (AlphaNum): ' + CASE 
--WHEN dbo.udfConvertBase10NumberToAnyBase(10000, 62, NULL) = '2bI'
--THEN 'SUCCESS' ELSE 'FAIL' END;
--
--SELECT 'TEST7 (ERROR-1):  ' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(512, 88, NULL) LIKE 'ERROR%' --Radix too large
--THEN 'SUCCESS' ELSE 'FAIL' END;
--
--SELECT 'TEST8 (DATAENTRY):' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(512, 32, '23456789ABCDEFGHIJKLMNPQRSTUVWXY') = 'I2'
--THEN 'SUCCESS' ELSE 'FAIL' END;
--
--SELECT 'TEST9 (CUSTOM):   ' + CASE
--WHEN dbo.udfConvertBase10NumberToAnyBase(8888, 30, '!@a#b$c%d^e&f*g:h[i+j-k9876654') = '^6d'
--THEN 'SUCCESS' ELSE 'FAIL' END;
--********************************************************************************
CREATE FUNCTION udfConvertBase10NumberToAnyBase
( @Base10Number BIGINT,
  @Radix   TINYINT,
  @NewBaseCharSet VARCHAR(128) --IF NOT NULL, overrides "standard" chars defined herein.
)
RETURNS VARCHAR(120)
AS
BEGIN
  --Exit immediately if meaningless conversion, returning NULL value.
  IF (@Base10Number IS NULL) OR (@Radix IS NULL) RETURN NULL;
  
  --This variable will hold all characters we permit in our new number-base.
  DECLARE @vCharset VARCHAR(128) = COALESCE(@NewBaseCharSet, 
      '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
  
  --If requested radix size > number of characters in conversion-characters-set, return error
  IF LEN(@vCharset) < @Radix
  BEGIN
    RETURN 'ERROR: udfConvertBase10NumberToAnyBase - Requested Radix exceeds @vCharset size';
  END;
  
  DECLARE @vNewNumber VARCHAR(120) = '';
  
  --******************************************************************************************
  -- Algorithm for generating equivalent number in the new "base" :
  -- 1) The original (base-10) number is continually divided by the new base (radix) until 
  --   the product of the original number divided by the radix is zero 
  --   (meaning the number is finally smaller than the new base).
  -- 2) On each cycle (loop iteration), the remainder (which forms each digit of the new base)
  --   of the MOD operation is prepended to the New Number.
  --******************************************************************************************
  WHILE @Base10Number <> 0
  BEGIN
    SET @vNewNumber = SUBSTRING(@vCharset, (@Base10Number % @Radix) + 1, 1) + @vNewNumber;
    SET @Base10Number /= @Radix; --PRE-SQL2008 (pre "/=" operator), use: @Base10Number = @Base10Number / @Radix
  END; --While
  
  RETURN @vNewNumber;
  
END --Function




--**********************************************************************************************
-- Convert a "number" from any Base (Radix) representation to a base-10 (i.e., Decimal) number.
-- This is the counterpart to the udfConvertBase10NumberToAnyBase function, and allows for 
-- restoring alphanumeric-equivalents of a base-10 counterpart to decimal form.
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
-- SET NOCOUNT ON
--
-- SELECT 'TEST1 (NULL-Cond):' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10(NULL, 2, NULL) IS NULL
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--
-- SELECT 'TEST2 (BINARY):   ' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('1010', 2, NULL) = 10
-- THEN 'SUCCESS' ELSE 'FAIL' END; 
--
-- SELECT 'TEST3 (HEX):      ' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('3E8', 16, NULL) = 1000 
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--
-- SELECT 'TEST4 (ALPHA26):  ' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('TS', 26, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = 512 
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--
-- SELECT 'TEST5 (ALPHANUM): ' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('RS', 36, NULL) = 1000
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--
-- SELECT 'TEST6 (AlphaNum): ' + CASE 
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('2bI', 62, NULL) = 10000
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--
-- SELECT 'TEST7 (ERROR-1):  ' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('abc', 88, NULL) < 0 --Radix too big / other error
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--
-- SELECT 'TEST8 (DATAENTRY):' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('I2', 32, '23456789ABCDEFGHIJKLMNPQRSTUVWXY') = 512 
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--
-- SELECT 'TEST9 (CUSTOM):   ' + CASE
-- WHEN dbo.udfConvertAnyBaseNumberToBase10('^6d', 30, '!@a#b$c%d^e&f*g:h[i+j-k9876654') = 8888
-- THEN 'SUCCESS' ELSE 'FAIL' END;
--**********************************************************************************************
CREATE FUNCTION udfConvertAnyBaseNumberToBase10
( @AnyBaseNumber VARCHAR(120),
  @Radix   TINYINT,
  @BaseCharSet VARCHAR(128) --IF NOT NULL, overrides "standard" chars defined herein.
)
RETURNS BIGINT
AS
BEGIN
  DECLARE @NumLength TINYINT = LEN(@AnyBaseNumber);
  
  --Exit immediately if meaningless conversion, returning NULL value.
  IF (@AnyBaseNumber IS NULL) OR (@Radix IS NULL) OR (@NumLength = 0) RETURN NULL;
  
  --This variable will hold all characters we permit in our new number-base.
  DECLARE @vCharset VARCHAR(128) = COALESCE(@BaseCharSet, 
      '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
  
  --If requested radix size > character-count in conversion-characters-set, return error
  IF LEN(@vCharset) < @Radix
  BEGIN
    RETURN -1;
  END;
  
  --Variable to hold our return value while working on it.
  DECLARE @Base10Number BIGINT = 0;
  
  --Variable to keep track of our conversion-position in string during loop 
  DECLARE @SearchPos TINYINT = 0;
  
  --Variable to hold location of character within allowed-chars (CharSet)
  DECLARE @CharSetPos TINYINT = 0;
  
  --******************************************************************************************
  -- Algorithm for generating equivalent number in the Base-10 :
  -- 1) Reverse the string so lower-order digits are at the left.  This makes calculating the
  --   Power of each position a 1:1 operation vs a calculation based on string-length. 
  -- 2) On each cycle (loop iteration), simply multiply the CharIndex (which converts a given
  --   Character to it's numeric value, by the Radix^(Search-Position's-Power).
  --   This works since reading number-to-convert from Right-To-Left essentially, and with
  --   each move "forward" while reading number increases our multiplier by another power.
  --******************************************************************************************
  SELECT  @AnyBaseNumber = REVERSE(@AnyBaseNumber);
  
  WHILE @SearchPos < @NumLength
  BEGIN
    SET @SearchPos += 1; --PRE-SQL2008 (pre "+=" operator), use: @SearchPos = @SearchPos + 1;
    SET @CharSetPos = CHARINDEX(SUBSTRING(@AnyBaseNumber, @SearchPos, 1), 
        @vCharset 
        COLLATE Latin1_General_CS_AS); --Case-Sensitive search!
    
    --Indicate error condition if character exists in our number-to-convert that is not 
    --in our charset used for conversion.
    IF @CharSetPos = 0 RETURN -2;
    
    SET @Base10Number += ((@CharSetPos-1) * POWER(@Radix,@SearchPos-1));
  END; --While
    
  RETURN @Base10Number;
  
END --Function

Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.