select right('000'+cast(id as varchar),4) as id from t1
select left('0000',4-len(cast(id as varchar(4))))+cast(id as varchar(4)) from t1 ======================================== http://www.dbtuning.cn 主营:中小企业数据库管理、优化、调校服务 ========================================
select replicate('0',4-len(id))+cast(id as varchar) as [id] from t1
create table t(id int) go insert t values(1) insert t values(2) insert t values(5) insert t values(10) insert t values(20) insert t values(100) insert t values(2000) select left('0000',4-len(cast(id as varchar(4))))+cast(id as varchar(4)) id from t /*结果 id -------- 0001 0002 0005 0010 0020 0100 2000 */======================================== http://www.dbtuning.cn 主营:中小企业数据库管理、优化、调校服务 ========================================
if object_id('t1') is not null drop table t1 go create table t1(id int) go insert t1 values(1) insert t1 values(2) insert t1 values(5) insert t1 values(10) insert t1 values(20) insert t1 values(100) insert t1 values(2000) select replicate('0',4-len(id))+cast(id as varchar) as [id] from t1 go /*id ---- 0001 0002 0005 0010 0020 0100 2000(所影响的行数为 7 行)*/
select right('0000' + cast(id as varchar),4) id from t1
create table tb(id int) insert tb values(2) insert tb values(10) insert tb values(120) insert tb values(6520) goselect right('0000' + cast(id as varchar),4) id from tbdrop table tb/* id -------- 0002 0010 0120 6520 */
right('0000'+cast(id as varchar),4) 在ID前面补全四个0,然后截取右边四位
========================================
http://www.dbtuning.cn
主营:中小企业数据库管理、优化、调校服务
========================================
select replicate('0',4-len(id))+cast(id as varchar) as [id] from t1
go
insert t values(1)
insert t values(2)
insert t values(5)
insert t values(10)
insert t values(20)
insert t values(100)
insert t values(2000)
select left('0000',4-len(cast(id as varchar(4))))+cast(id as varchar(4)) id from t
/*结果
id
--------
0001
0002
0005
0010
0020
0100
2000
*/========================================
http://www.dbtuning.cn
主营:中小企业数据库管理、优化、调校服务
========================================
drop table t1
go
create table t1(id int)
go
insert t1 values(1)
insert t1 values(2)
insert t1 values(5)
insert t1 values(10)
insert t1 values(20)
insert t1 values(100)
insert t1 values(2000) select replicate('0',4-len(id))+cast(id as varchar) as [id] from t1
go
/*id ----
0001
0002
0005
0010
0020
0100
2000(所影响的行数为 7 行)*/
select right('0000' + cast(id as varchar),4) id from t1
insert tb values(2)
insert tb values(10)
insert tb values(120)
insert tb values(6520)
goselect right('0000' + cast(id as varchar),4) id from tbdrop table tb/*
id
--------
0002
0010
0120
6520
*/
在ID前面补全四个0,然后截取右边四位