select lengthb('長度6a'),length('長度6a') from dual;
lengthb('長度6a')=8 length('長度6a')=4
一般我們那asci 碼裡面的下滑線來判斷是否有漢字(來源于網絡)
select asciistr(l.company_short_name),l.company_short_name,
--INSTR(asciistr(prod_name),'\', 1, 1),
substr(asciistr(l.company_short_name),1,INSTR(asciistr(l.company_short_name),'\', 1, 1)-1),
substr(l.company_short_name,length(substr(asciistr(l.company_short_name),1,INSTR(asciistr(l.company_short_name),'\', 1, 1)-1))+1)
from fnd_companies_vl l where asciistr(l.company_short_name) like '%\%' and instr(l.company_short_name, '\') <= 0 and l.company_id=1104123 ;
最後寫法
SELECT COUNT(1)
INTO v_place_num
FROM pur_lines_all l
WHERE (trim(l.production_location) IS NULL OR
(ASCIISTR(trim(l.production_location)) NOT like '%\%') OR
(ASCIISTR(trim(l.production_location)) like '%\%' AND lengthb(trim(l.production_location))<6 )
) AND l.pur_header_id= p_pur_header_id;
IF(v_place_num>0) THEN
RAISE e_place_null_err;
END IF;