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 replace(price,'¥','') between 5 and 9 我测试的时候,查询大于“>”或小于“<”某个价格,如:0.5或者一个整数价格的时候会报无效字符的错误,这个一般是什么情况下出现?怎么解决? 也用 select * from tab where replace(price,'¥','') between 0.1 and 9 进行测试,同样会报错。
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 9ltrim函数的使用,可以实现
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
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
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
我测试的时候,查询大于“>”或小于“<”某个价格,如:0.5或者一个整数价格的时候会报无效字符的错误,这个一般是什么情况下出现?怎么解决?
也用 select * from tab where replace(price,'¥','') between 0.1 and 9 进行测试,同样会报错。
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 9ltrim函数的使用,可以实现