Converting Between Base 2, 10 and 16 in T-SQL

本文介绍如何在十进制、十六进制和二进制之间进行数值转换,包括使用Google、Windows计算器以及T-SQL查询的方法。特别介绍了在T-SQL中实现二进制到十进制及反之的转换函数。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

There are many numeral systems, the most common ones in computer science being binary (base 2), decimal (base 10) and hexadecimal (base 16). All numbers can be expressed in either system and you may now and then need to convert between them.

Take the number 493.202.384 as an example, it can be be expressed as either 0n493202384 in decimal, 0x1D65ABD0 in hexadecimal or 0b11101011001011010101111010000 in binary. Note how the 0n prefix declares a decimal value, 0x a hexadecimal and 0b a binary value.

Converting using Google

If you’ve got an internet connection, the quickest and simplest way is often to just use Google. We can convert the above number using “in X” queries:

493202384 in hex
493202384 in binary

Converting using Windows Calculator

You can also open Windows Calculator, switch to the programmer mode and type in the decimal value (or the hex/binary value):

And from then on we can just switch the numerical system selector to the left:

Converting between decimal & hex in T-SQL

Sometimes however, it’s just a tad easier if we could do it directly from a T-SQL query. Converting between decimal and hexadecimal is straightforward and can be done using just built in functions:

-- Decimal to hex
SELECT CAST(493202384 AS varbinary)

-- Hex to decimal
SELECT CAST(0x1D65ABD0 AS int)

-- Decimal to hex to decimal
SELECT CAST(CAST(493202384 AS varbinary) AS int)

Converting binary to decimal using T-SQL

Converting to/from binary is a bit more tricky though, as there are no built in functions for formatting a decimal number as a binary string, nor converting the latter to the first.

The following function takes in a binary string and returns a bigint with the decimal value:

CREATE FUNCTION [dbo].[BinaryToDecimal]
(
	@Input varchar(255)
)
RETURNS bigint
AS
BEGIN

	DECLARE @Cnt tinyint = 1
	DECLARE @Len tinyint = LEN(@Input)
	DECLARE @Output bigint = CAST(SUBSTRING(@Input, @Len, 1) AS bigint)

	WHILE(@Cnt < @Len) BEGIN
		SET @Output = @Output + POWER(CAST(SUBSTRING(@Input, @Len - @Cnt, 1) * 2 AS bigint), @Cnt)

		SET @Cnt = @Cnt + 1
	END

	RETURN @Output	

END

The function looks at each char in the input string (starting from behind), adding POWER(2, @Cnt) to the result if the bit is set – with special handling of the first (that is, from behind) character since POWER(2, 0) is 1 while we need it to be 0.

Usage is straight forward:

SELECT dbo.BinaryToDecimal('11101011001011010101111010000')

Converting decimal to binary using T-SQL

The following function takes a bigint as input and returns a varchar with the binary representation, using the short division by two with remainder algorithm:

CREATE FUNCTION [dbo].[DecimalToBinary]
(
	@Input bigint
)
RETURNS varchar(255)
AS
BEGIN

	DECLARE @Output varchar(255) = ''

	WHILE @Input > 0 BEGIN

		SET @Output = @Output + CAST((@Input % 2) AS varchar)
		SET @Input = @Input / 2

	END

	RETURN REVERSE(@Output)

END

Again usage is straight forward:

SELECT dbo.DecimalToBinary(493202384)

Ensuring correctness

A simple test to ensure correct conversions would be to convert from A to B and back to A again, using both of the above functions. Thus whatever we give as input should be the output as well:

SELECT dbo.DecimalToBinary(dbo.BinaryToDecimal('11101011001011010101111010000'))
SELECT dbo.BinaryToDecimal(dbo.DecimalToBinary(493202384))

Et voilá! Once we have the functions, they can easily be used in a normal query:

SELECT
	object_id,
	CAST(object_id AS varbinary) AS object_id_hex,
	dbo.DecimalToBinary(object_id) AS object_id_binary
FROM
	sys.objects

基于开源大模型的教学实训智能体软件,帮助教师生成课前备课设计、课后检测问答,提升效率与效果,提供学生全时在线练习与指导,实现教学相长。 智能教学辅助系统 这是一个智能教学辅助系统的前端项目,基于 Vue3+TypeScript 开发,使用 Ant Design Vue 作为 UI 组件库。 功能模块 用户模块 登录/注册功能,支持学生和教师角色 毛玻璃效果的登录界面 教师模块 备课与设计:根据课程大纲自动设计教学内容 考核内容生成:自动生成多样化考核题目及参考答案 学情数据分析:自动化检测学生答案,提供数据分析 学生模块 在线学习助手:结合教学内容解答问题 实时练习评测助手:生成随练题目并纠错 管理模块 用户管理:管理员/教师/学生等用户基本管理 课件资源管理:按学科列表管理教师备课资源 大屏概览:使用统计、效率指数、学习效果等 技术栈 Vue3 TypeScript Pinia 状态管理 Ant Design Vue 组件库 Axios 请求库 ByteMD 编辑器 ECharts 图表库 Monaco 编辑器 双主题支持(专业科技风/暗黑风) 开发指南 # 安装依赖 npm install # 启动开发服务器 npm run dev # 构建生产版本 npm run build 简介 本项目旨在开发一个基于开源大模型的教学实训智能体软件,帮助教师生成课前备课设计、课后检测问答,提升效率与效果,提供学生全时在线练习与指导,实现教学相长。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值