Geeks With Blogs

News


Brian Biales because blogging is just the easiest way to remember things

I just found a great post at SQL Tips by Namwar Rizvi - TSQL Function to convert decimal to Hex, Octal or any other base (http://sqltips.wordpress.com/) with a great, simple function to convert any integer into a string of characters in any base from 2 through 36. 

Here it is, the comments include his post text, giving original credit for this algorithm to Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying:

IF EXISTS 
(
   SELECT * FROM sysobjects
     WHERE id = OBJECT_ID(N'[dbo].[fn_decToBase]')
       AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
   DROP FUNCTION [dbo].[fn_decToBase]
Go
 
Create function [dbo].[fn_decToBase]
(
@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(36);
  Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 
  /* 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
Posted on Monday, May 4, 2009 2:54 PM | Back to top


Comments on this post: TSQL - convert any integer to a string base 2 through 36

# re: TSQL - convert any integer to a string base 2 through 36
Requesting Gravatar...
Great code snippet!!!
Left by Nock on Jun 13, 2009 6:04 PM

# re: TSQL - convert any integer to a string base 2 through 36
Requesting Gravatar...
Does not convert negative decimal values to hexidecimal.
Left by Steve on Jul 13, 2009 2:40 PM

# re: TSQL - convert any integer to a string base 2 through 36
Requesting Gravatar...
that's great! thanks alot
Left by Mina on Apr 12, 2011 8:13 PM

# re: TSQL - convert any integer to a string base 2 through 36
Requesting Gravatar...
You could get rid of the letter matrix:

create function [dbo].[fn_decToBase]
(
@val as BigInt,
@base as int
)
returns varchar(63)
as
Begin
DECLARE @Result VARCHAR(13) = ''

WHILE (@Val > 0)
BEGIN
SELECT @Result = CHAR(@Val % @base + CASE WHEN @Val % @base < 10 THEN 48 ELSE 55 END) + @Result,
@Val = FLOOR(@Val/@base)
END

RETURN @Result
End
Left by Tamaas on Apr 07, 2018 7:47 AM

Your comment:
 (will show your gravatar)


Copyright © Brian Biales | Powered by: GeeksWithBlogs.net