联系:手机/微信(+86 17813235971) QQ(107644445)
标题:oracle分割函数
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1、创建varry
CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
2、创建分割函数
CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2) RETURN varchar2varray IS len INTEGER := LENGTH (substring); lastpos INTEGER := 1 - len; pos INTEGER; num INTEGER; i INTEGER := 1; ret varchar2varray := varchar2varray (NULL); v_str VARCHAR2 (20); /**自定义split函数,将指定的字符串按指定的标志符分割成字符数组*/ BEGIN LOOP pos := INSTR (STRING, substring, lastpos + len); IF pos > 0 THEN --found num := pos - (lastpos + len); ELSE --not found num := LENGTH (STRING) + 1 - (lastpos + len); END IF; IF i > ret.LAST THEN ret.EXTEND; END IF; v_str := SUBSTR (STRING, lastpos + len, num); --DBMS_OUTPUT.put_line (v_str); ret(i) := v_str; EXIT WHEN pos = 0; lastpos := pos; i := i + 1; END LOOP; RETURN ret; END;
3、调用
select * from tab_dailyreport_user where user_id in ( SELECT * FROM ( TABLE( select f_strsplit(user_invite,',') from tab_dailyreport_user t where t.user_id = 168148)));
说明:主要是改写开发提过来的一个sql语句的帮助
select * from tab_dailyreport_user where user_id in ( --'166445','164216','171128','184427','160113','160133','160134','160138','160144','160163','160587','166457','167914','168076','168192','168997','169842','169901','184407','184747','185089','185130','208134','208141','208143','208183','160142','166455','167838','168074','168194','168666','185088','185138','185145','208103','169844','171071','160107','166421','166874','168193','179871','179872','184096','184228','184232','184269','184774','184969','185111','207871','160167','161813','14880','134','164355','168149') select substr(regexp_replace,3,length(regexp_replace)-4) from ( select regexp_replace((select user_invite from tab_dailyreport_user t where t.user_id = 168148), '(,)', chr(39)||','||chr(39)) regexp_replace from dual) )
括号里面的语句查询出来的结果是注释部分,直接使用注释的部分在in中可以查询,如果使用里面的语句不能查询,他们的需求是想把外面的user_id在里面的语句中的,可是oracle会把里面的in查询出来的结果作为一个整体,从而出现number类型不能和varchar类型匹配的提示,采用方法是分割in里面查询出来的语句,然后类型转换为table进行查询。
拼接函数(10g及其以上版本)
wmsys.wm_concat