22,207
社区成员
发帖
与我相关
我的任务
分享
col newcol
-------------------- --------------------
以市场价为准 .00
10。35万元 10.35
25。68万 25.68
35。00 35.00
(所影响的行数为 4 行)
create table tb(col varchar(20))
insert into tb values('10。35万元')
insert into tb values('25。68万')
insert into tb values('35。00')
insert into tb values('以市场价为准')
go
select col ,
newcol = case when ISNUMERIC(replace(replace(replace(col,'万',''),'元',''),'。','')) = 0 then 0 else cast(replace(replace(replace(col,'万',''),'元',''),'。','') as decimal(18,2)) end
from tb order by newcol
drop table tb
/*
col newcol
-------------------- --------------------
以市场价为准 .00
10。35万元 1035.00
25。68万 2568.00
35。00 3500.00
(所影响的行数为 4 行)
*/
create table tb(col varchar(20))
insert into tb values('10。万元')
insert into tb values('25。万')
insert into tb values('35。')
insert into tb values('以市场价为准')
go
select col,
case when col='以市场价为准' then 0
else (cast( (left(col,charindex('。',col)-1)+'.'+
substring(col,charindex('。',col)+1,2))
as decimal(10,2))*
(case when charindex('万',col)>0 then 10000 else 1 end))
end as col1
from tb order by col1 desc
/*
col col1
25。68万 256800.00
10。35万元 103500.00
35。00 35.00
以市场价为准 0.00
*/
select col,
case when (col regexp '^[0-9]*$')=1 then
(cast( (left(col,charindex('。',col)-1)+'.'+
substring(col,charindex('。',col)+1,2))
as demical(10,2))*
(case when charindex('万',col)>0 then 10000 else 1 end))
else 0 end as col
from [table]
--mysql没有isnumber函数
select col ,
newcol = case when col='以市场价为准' then '0' else replace(replace(replace(col,'万','0000'),'元',''),'。','') end
from tb order by newcol