--自己决定是否需要几位小数或不要小数 select cast(col as numeric(18,2)) from tb where ISNUMERIC(col) = 1select cast(col as numeric(18,1)) from tb where ISNUMERIC(col) = 1select cast(col as numeric(18)) from tb where ISNUMERIC(col) = 1
select convert(numeric(18,2),col)from tb where ISNUMERIC(col) = 1
create table tb(id int , col varchar(10)) insert into tb values(1 , '1') insert into tb values(1 , '10') insert into tb values(1 , '11') insert into tb values(1 , '12') insert into tb values(1 , '13') go --原来的数据 select * from tb /* id col ----------- ---------- 1 1 1 10 1 11 1 12 1 13(所影响的行数为 5 行) */select id , col = cast(col as numeric(18,2)) from tb where ISNUMERIC(col) = 1 /* id col ----------- -------------------- 1 1.00 1 10.00 1 11.00 1 12.00 1 13.00(所影响的行数为 5 行) */select id , col = cast(col as numeric(18,1)) from tb where ISNUMERIC(col) = 1 /* id col ----------- -------------------- 1 1.0 1 10.0 1 11.0 1 12.0 1 13.0(所影响的行数为 5 行) */select id , col = cast(col as numeric(18)) from tb where ISNUMERIC(col) = 1 /* id col ----------- -------------------- 1 1 1 10 1 11 1 12 1 13(所影响的行数为 5 行) */drop table tb
如果要改表结构,直接用企业管理器改比较快,如果保存失败,也不会丢数据的.自己写SQL多麻烦啊
select Convert(numeric(18,2),column)from table
alter table tb alter column 列名 numeric(18,2)
直接用convert或cast修改就可以了.
select cast(col as numeric(18,X)) from tb where ISNUMERIC(col) = 1 X>=0的int数
--自己决定是否需要几位小数或不要小数
select cast(col as numeric(18,2)) from tb where ISNUMERIC(col) = 1select cast(col as numeric(18,1)) from tb where ISNUMERIC(col) = 1select cast(col as numeric(18)) from tb where ISNUMERIC(col) = 1
insert into tb values(1 , '1')
insert into tb values(1 , '10')
insert into tb values(1 , '11')
insert into tb values(1 , '12')
insert into tb values(1 , '13')
go
--原来的数据
select * from tb
/*
id col
----------- ----------
1 1
1 10
1 11
1 12
1 13(所影响的行数为 5 行)
*/select id , col = cast(col as numeric(18,2)) from tb where ISNUMERIC(col) = 1
/*
id col
----------- --------------------
1 1.00
1 10.00
1 11.00
1 12.00
1 13.00(所影响的行数为 5 行)
*/select id , col = cast(col as numeric(18,1)) from tb where ISNUMERIC(col) = 1
/*
id col
----------- --------------------
1 1.0
1 10.0
1 11.0
1 12.0
1 13.0(所影响的行数为 5 行)
*/select id , col = cast(col as numeric(18)) from tb where ISNUMERIC(col) = 1
/*
id col
----------- --------------------
1 1
1 10
1 11
1 12
1 13(所影响的行数为 5 行)
*/drop table tb
select Convert(numeric(18,2),column)from table
alter column 列名 numeric(18,2)
X>=0的int数