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;