--created by : wanglin
--created date : 2012-08-20
--created purpose: instr()函数,特殊用法,代替like,in
instr()返回值是目标字符(串)在母字符里第一次出现的位置,故而是整数。
例如:
instr("efabcdefg","e")的结果当然是1
instr(2,"efabcdefg","e")的结果就是7
instr(5,"efabcdefg","a")的结果就是0
instr("efabcdefg","k")的结果也是0
instr是内置函数,他是会走索引的,性能好。
like不一定:like '%iii%'是不走索引的,like 'ik%'走索引。
例如:
代替like:
SELECT code, name, dept, occupation FROM staff WHERE instr(code, '001') > 0;
等同于
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '%001%' ;
代替in:
SELECT code , name , dept, occupation FROM staff WHERE code IN ('A10001','A10002');
等同于
SELECT code , name , dept, occupation FROM staff WHERE instr('A10001,A10002',code)>0
instr()函数,特殊用法,代替like,in
最新推荐文章于 2023-05-07 16:18:21 发布