with tbl as ( select 'abc.#中国' as text from dual union all select '凤凰' from dual union all select 'a小鱼儿b' from dual union all select '测b试e' from dual ) select text, length(text) - nvl(length(regexp_replace(text, '[A-Za-z]', '')),0) as "英文长度", (lengthb(text) - length(text)) / (lengthb('一') - 1) as "中文长度" --不同字符集一个中文占位长度不一样,所以随便用一个中文来计算长度 from tbl;TEXT 英文长度 中文长度 ----------- ------------ ------------ abc.#中国 3 2 凤凰 0 2 a小鱼儿b 2 3 测b试e 2 2
with tbl as
(
select 'abc.#中国' as text from dual
union all
select '凤凰' from dual
union all
select 'a小鱼儿b' from dual
union all
select '测b试e' from dual
)
select text,
length(text) - nvl(length(regexp_replace(text, '[A-Za-z]', '')),0) as "英文长度",
(lengthb(text) - length(text)) / (lengthb('一') - 1) as "中文长度" --不同字符集一个中文占位长度不一样,所以随便用一个中文来计算长度
from tbl;TEXT 英文长度 中文长度
----------- ------------ ------------
abc.#中国 3 2
凤凰 0 2
a小鱼儿b 2 3
测b试e 2 2