declare @table table
(
val varchar(10)
)
insert into @table values('10v'),('220v'),('380v'),('20kv'),('30kv'),('50kv')
;with cte as(
select
val,
case
when CHARINDEX('kv',val)>0 then LEFT(val,CHARINDEX('kv',val)-1)*1000
else LEFT(val,CHARINDEX('v',val)-1)
end as vals
from @table
)
select val
from cte
where vals>200
val
----------
220v
380v
20kv
30kv
50kv
(5 行受影响)
declare @table table
(
val varchar(10)
)
insert into @table values('10v'),('220v'),('380v'),('20kv'),('30kv'),('50kv')
select val
from @table
where convert(int,replace(REPLACE(val,'k','000'),'v',''))>200
