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

Popular Posts on Geeks with Blogs 0
Geeks With Blogs Content Categories ASP.Net SQL Server Apple Google SharePoint Windows Visual Studio Team Foundation Server Agile Office Design Patterns Web Azure
Brand New Posts on Geeks with Blogs 0