VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
720512 29 1 NULL NULL 0 1 1 NULL
720513 142 1 NULL NULL 0 1 1 NULL
结果如下:
VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
0720512 29 1 NULL NULL 0 1 1 NULL
0720513 142 1 NULL NULL 0 1 1 NULL
720512 29 1 NULL NULL 0 1 1 NULL
720513 142 1 NULL NULL 0 1 1 NULL
结果如下:
VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
0720512 29 1 NULL NULL 0 1 1 NULL
0720513 142 1 NULL NULL 0 1 1 NULL
set VIPID=right('0000000'+VIPID,7)
where len(VIPID)<7
go
create table [tb]([VIPID] varchar(10),[Point] varchar(10),[Level] int,[VIPName] varchar(10),[Sex] varchar(10),[IDCard] int,[Para1] int,[Para2] int,[Phone] varchar(10))
insert [tb] select '720512','29',1,NULL,NULL,0,1,1,NULL
union all select '720513','142',1,NULL,NULL,0,1,1,NULL
goupdate tb set [VIPID]=right('0000000'+[VIPID],7)select * from tb
/*
VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
---------- ---------- ----------- ---------- ---------- ----------- ----------- ----------- ----------
0720512 29 1 NULL NULL 0 1 1 NULL
0720513 142 1 NULL NULL 0 1 1 NULL(2 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([VIPID] varchar(10),[Point] int,[Level] int,[VIPName] varchar(20),[Sex] varchar(5),[IDCard] int,[Para1] int,[Para2] int,[Phone] varchar(10))
insert [tb]
select 720512,29,1,null,null,0,1,1,null union all
select 720513,142,1,null,null,0,1,1,null
---更新---
update tb
set VIPID=right('0000000'+VIPID,7)
where len(VIPID)<7---查询---
select * from [tb]
---结果---
VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
---------- ----------- ----------- -------------------- ----- ----------- ----------- ----------- ----------
0720512 29 1 NULL NULL 0 1 1 NULL
0720513 142 1 NULL NULL 0 1 1 NULL(所影响的行数为 2 行)
go
create table [tb]([VIPID] int,[Point] varchar(10),[Level] int,[VIPName] varchar(10),[Sex] varchar(10),[IDCard] int,[Para1] int,[Para2] int,[Phone] varchar(10))
insert [tb] select '720512','29',1,NULL,NULL,0,1,1,NULL
union all select '720513','142',1,NULL,NULL,0,1,1,NULL
go
--如果VIPID为INT型:
--修改字段类型:
alter table tb alter column [VIPID] varchar(10)
go
--更新:
update tb set [VIPID]=right('0000000'+[VIPID],7)
--查询
select * from tb
/*
VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
---------- ---------- ----------- ---------- ---------- ----------- ----------- ----------- ----------
0720512 29 1 NULL NULL 0 1 1 NULL
0720513 142 1 NULL NULL 0 1 1 NULL(2 行受影响)
*/
--更新:
update tb set [VIPID]=replicate('0',7-len([VIPID]))+[VIPID]
--查询
select * from tb
/*
VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
---------- ---------- ----------- ---------- ---------- ----------- ----------- ----------- ----------
0720512 29 1 NULL NULL 0 1 1 NULL
0720513 142 1 NULL NULL 0 1 1 NULL(2 行受影响)
*/
update tb set VIPID=right('0000000+VIPID',7)
update tablename set VIPID=right('0000000'+VIPID,7) where len(VIPID)<7
int会自动去掉前面的0,如果要更新则需要把这个字段改为字符型
或者查询的时候直接加0---查询---
select
VIPID=right('0000000'+ltrim(VIPID),7),
Point,
[Level],
VIPName,
Sex,
IDCard,
Para1,
Para2,
Phone
from [tb]
---结果---
VIPID Point Level VIPName Sex IDCard Para1 Para2 Phone
-------------- ----------- ----------- -------------------- ----- ----------- ----------- ----------- ----------
0720512 29 1 NULL NULL 0 1 1 NULL
0720513 142 1 NULL NULL 0 1 1 NULL(所影响的行数为 2 行)
若要在查询结果中显示满7位,可通过一下一句就搞定
select
VIPID=right('0000000'+ltrim(VIPID),7),
Point,
[Level],
VIPName,
Sex,
IDCard,
Para1,
Para2,
Phone
from [tb]
VIPID=right('0000000'+ltrim(VIPID),7),
Point,
[Level],
VIPName,
Sex,
IDCard,
Para1,
Para2,
Phone
from [tb]