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