还有一种写起来比较麻烦,通过select语句获取分割点 假设字符串为str WITH T AS ( select ,ROWNUM RN,LENGTHB(substr(str,rownum,1)) L from dual CONNECT BY ROWNUM<=LEAST(5000,LENGTH(STR)) ) SELECT max(RN) 字符数,SUBSTR(STR,1,MAX(RN)) 截取的第一个字符串 FROM (SELECT RN,L,SUM(L)OVER(ORDER BY RN) S FROM T) WHERE S<=5000
上面多写了个逗号,另外你字段长度为3000,我写的5000,修正下,你去执行下试试 WITH T AS ( select ROWNUM RN,LENGTHB(substr(str,rownum,1)) L from dual CONNECT BY ROWNUM<=LEAST(3000,LENGTH(STR)) ) SELECT max(RN) 字符数,SUBSTR(STR,1,MAX(RN)) 截取的第一个字符串 FROM (SELECT RN,SUM(L)OVER(ORDER BY RN) S FROM T) WHERE S<=3000
通过oracle lengthb函数获取字节数,字节数超出则停止
将之前取得的字符串存入字段
然后从剩下的字符中用同样方法获取下一个字段
假设字符串为str
WITH T AS (
select ,ROWNUM RN,LENGTHB(substr(str,rownum,1)) L from dual
CONNECT BY ROWNUM<=LEAST(5000,LENGTH(STR))
)
SELECT max(RN) 字符数,SUBSTR(STR,1,MAX(RN)) 截取的第一个字符串 FROM
(SELECT RN,L,SUM(L)OVER(ORDER BY RN) S
FROM T)
WHERE S<=5000
WITH T AS (
select ROWNUM RN,LENGTHB(substr(str,rownum,1)) L from dual
CONNECT BY ROWNUM<=LEAST(3000,LENGTH(STR))
)
SELECT max(RN) 字符数,SUBSTR(STR,1,MAX(RN)) 截取的第一个字符串 FROM
(SELECT RN,SUM(L)OVER(ORDER BY RN) S
FROM T)
WHERE S<=3000