表：
name price
a ￥5.22
b 9.50
c 8.00
d
e ￥0.10
f 4.05
其中 name和price都是varchar2 数据库为 oracle
需要查询价格在某个区间的商品和价格
如：在5到9和之间的所有name和price，￥不要显示出来。
------解决方案--------------------
with tab as(
select 'a' name,'￥5.22' price from dual union all
select 'b' name,'9.50' price from dual union all
select 'c' name,'8.00' price from dual union all
select 'd' name,'' price from dual union all
select 'e' name,'￥0.10' price from dual union all
select 'f' name,'4.05' price from dual
)
select * from tab where replace(price,'￥','') between 5 and 9
------解决方案--------------------
写个函数判断一下
CREATE OR REPLACE FUNCTION fun_price(
v_price T_T.PRICE%TYPE)
RETURN T_T.PRICE%TYPE IS
BEGIN
if(substr(v_price,0,1)='￥') THEN
RETURN substr(v_price,2,length(v_price));
ELSE
RETURN v_price;
END IF;
END;
SELECT t.name,fun_price(t.price) FROM t_t t
------解决方案--------------------
with tab as(
select 'a' name,'￥5.22' price from dual union all
select 'b' name,'9.50' price from dual union all
select 'c' name,'8.00' price from dual union all
select 'd' name,'' price from dual union all
select 'e' name,'￥0.10' price from dual union all
select 'f' name,'4.05' price from dual )
select * from tab where LTRIM(price,'￥') between 5 and 9
ltrim函数的使用，可以实现