表里两个字段:a(int,递增),b(varchar)如果表里数据是:
a b
1 1.02
2 2.02
3 3.00
4 5.32
5 1.00
那么怎么取数,使1.00变为1,但5.32还是5.32,就是有小数的仍然保持有小数,没有小数就化为整数。
a b
1 1.02
2 2.02
3 3.00
4 5.32
5 1.00
那么怎么取数,使1.00变为1,但5.32还是5.32,就是有小数的仍然保持有小数,没有小数就化为整数。
col,
col_convert = CASE
WHEN CHARINDEX('.', col) = 0
THEN col
WHEN RIGHT(col, PATINDEX('%[^0]%', REVERSE(col))) LIKE '.%'
THEN LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)))
ELSE LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)) + 1)
END
FROM(
SELECT col = '100' UNION ALL
SELECT col = NULL UNION ALL
SELECT col = '.100' UNION ALL
SELECT col = '.100100' UNION ALL
SELECT col = '0.' UNION ALL
SELECT col = '0' UNION ALL
SELECT col = '100.1010' UNION ALL
SELECT col = '100.0000'
)A-- 结果:
col col_convert
-------- -----------
100 100
NULL NULL
.100 .1
.100100 .1001
0. 0
0 0
100.1010 100.101
100.0000 100(8 行受影响)
insert into tb values(1 , 1.02)
insert into tb values(2 , 2.02)
insert into tb values(3 , 3.00)
insert into tb values(4 , 5.32)
insert into tb values(5 , 1.00)
goselect a , b = case when cast(b*100 as int)%100 = 0 then cast(cast(b*100/100 as int) as varchar) else cast(b as varchar) end from tbdrop table tb/*
a b
----------- ------------------------------
1 1.02
2 2.02
3 3
4 5.32
5 1(所影响的行数为 5 行)
*/
convert什么之类的?不要有一个单独的函数呢。
insert into tb (b) select 1.02 union all select 2.02 union all select 3.00 union all select 5.32 union all select 1.00
go
select a,(case when floor(b)=b then convert(varchar(8),floor(b)) else convert(varchar(10),b) end) b from tb
go
drop table tb
/*
a b
----------- ----------
1 1.02
2 2.02
3 3
4 5.32
5 1(5 行受影响)
*/
insert into tb values(1 , 1.02)
insert into tb values(2 , 2.02)
insert into tb values(3 , 3.00)
insert into tb values(4 , 5.32)
insert into tb values(5 , 1.00)
goselect a , b = case when cast(b*100 as int)%100 = 0 then cast(cast(b as int) as varchar) else cast(b as varchar) end from tbdrop table tb/*
a b
----------- ------------------------------
1 1.02
2 2.02
3 3
4 5.32
5 1(所影响的行数为 5 行)
*/
create table tb
(
a int identity(1,1),
b varchar(50)
)insert into tbselect 1.02 union all
select 2.02 union all
select 3.00 union all
select 5.32 union all
select 1.00 select
a,
case when right(b,2) = '00' then
left(b,charindex('.',b) - 1)
else
b
end b
from tbdrop table tb
结果:
1 1.02
2 2.02
3 3
4 5.32
5 1
declare @a table (a int identity(1,1),b varchar(10))
insert into @a values ('1.02')
insert into @a values ('2.02')
insert into @a values ('3.00')
insert into @a values ('5.32')
insert into @a values ('1.00')select a,cast(b as float) as b from @a
select a,cast(cast(b as float)as varchar) as b from @a
create table tb
(
a int identity(1,1),
b varchar(50)
)insert into tbselect 1.02 union all
select 2.02 union all
select 3.00 union all
select 5.32 union all
select 1.00 select
a,
cast(b as float) bfrom tbdrop table tb
结果:
1 1.02
2 2.02
3 3
4 5.32
5 1
drop table tb
Go
Create table tb([a] int,[b] varchar(10))
Insert tb
select 1,'1.02' union all
select 2,'2.02' union all
select 3,'3.00' union all
select 4,'5.32' union all
select 5,'1.00'
Goselect a,b=rtrim(cast(b as float)) from tb
/*
a b
----------- -----------------------
1 1.02
2 2.02
3 3
4 5.32
5 1*/
declare @A table (a int,b decimal(10,2))
insert into @A select 1,1.02
union all select 2,2.02
union all select 3,3.00
union all select 4,5.32
union all select 5,1.00
--select * from @A
-----------------------
-----------------------
--select * from @A
select a,b=case when b*100 %100=0 then cast(cast( b as int) as varchar(5)) else cast( b as varchar(5)) end
from @a
1 1.02
2 2.02
3 3
4 5.32
5 1
insert into #tb values(1 , 1.02)
insert into #tb values(2 , 2.02)
insert into #tb values(3 , 3.00)
insert into #tb values(4 , 5.32)
insert into #tb values(5 , 1.00) select a,Replace(cast(b as varchar(20)),'.00','') as b from #tb
/*
a b
1 1.02
2 2.02
3 3
4 5.32
5 1
*/