日期:2014-05-17 浏览次数:20802 次
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