T-SQL使用案例——结果数据前面自动补0

在开发的过程中，往往需要数字和字符串互转。在转换的过程中，可能需要把1编程00001,这样的格式。实现这种样子是有非常多的方法，本文主要提供一种自定义函数来实现

一般来说这种处理主要使用CONVERT或者CAST来实现即可。思路是先转换为字符串然后在不上足够数量的0，最后使用RIGHT函数，从右边取出需要长度。

本例中使用AdventureWork数据库作为例子。

首先，执行下面语句：

--查询部门数据

SELECT DepartmentID,Name

FROM AdventureWorks.HumanResources.Department

结果如下：

然后，使用CAST将原来部门编号转换成VARCHAR()字符串。接着加上固定数量的0，然后利用RIGHT函数，从右边去除指定数量的字符串。

代码如下：

--查询部门数据并将数字根据位数补上0

SELECT RIGHT(REPLICATE('0',5)+CAST(DepartmentIDAS varchar(10)),5)AS 'DepartmentID',Name

FROM AdventureWorks.HumanResources.Department

ORDER BYDepartmentID

结果：

以上的是准备工作，现在开始编写一个自定义函数来实现，此函数有两个参数，第一个参数是要补0或者其他数目的个数，第二个是要转换的数字。最后把结果以字符串方式返回：

函数代码:

CREATE FUNCTIONfnZero

(

@n INT,

@i INT

)

RETURNS VARCHAR(125)

AS

BEGIN

RETURN

(

RIGHT(REPLICATE('0',@n)+CAST(@ias varchar(125)),@n)

)

END

使用例子:

--查询部门数据并将数字根据位数补上0

SELECT DepartmentIDas 'DepartmentID',dbo.fnZero(5,DepartmentID)as 'DepartmentID'

FROM AdventureWorks.HumanResources.Department

ORDER BY 1

注意,由于两个列都是同名,所以ORDER BY 那里使用了1,而不是列名.

结果如下:

当然,也可以改进该函数,使得函数能满足很多要求.这部分请读者自己去研究了.

3楼hopkinsa昨天 15:55

我太蠢了, 自己特地写了个func() 将字符化为 varhcar() 然后读取长度,再减去要求的字常再补零, 效率低下又繁琐.还是楼主的方法好阿

Re: DBA_Huangzj昨天 22:04

回复hopkinsan呵呵，大家分享一下而已。

2楼sjyandgj昨天 14:31

可以参考oracle写个lpad，rpad函数，因为i而左右都可能要补充数据

Re: DBA_Huangzj昨天 15:47

回复sjyandgjn有语句可以看看吗？

1楼lidaasky昨天 14:29

学习了