regexp_replace vs regexp_substr和使用将数据从一个单元格移动到许多单元格

Question

我一直得到一个[Teradata数据库][9134]输出超过缓冲区限制。

select 
MHKAUDITLOGINTERNALID as id, 
Cast(MHK_CONTENT as CLOB)as MHK_CONTENT 
from vcoreMEDHOK_MHK_Audit 
Where cast(AUDITDATETIME as Date) >= '2021/08/15' )
--- and id ='31181086' )
select ID, regexp_replace(Token, '<b>|</b>') -- trim everything up to '<b>' 
FROM TABLE (RegExp_Split_To_Table(t.id, t.MHK_CONTENT,'<BR/> *', 'i') -- split whenever '</b>' occurs 
RETURNS ( id BIGINT, TokenNum INT, Token VARCHAR(35000) CHARACTER SET LATIN)) AS dt 

有人知道如何绕过这个吗?[Teradata数据库][9134]输入超过缓冲区大小限制。

with t as
(
select MHKAUDITLOGINTERNALID  as id, cast(MHK_CONTENT as clob) as MHK_CONTENT
from vcoreMEDHOK_MHK_Audit as ma
Where cast(ma.AUDITDATETIME as Date) >= '2021/08/15'

)
SELECT
DISTINCT id,  
regexp_substr(MHK_CONTENT, '.*<b>K.*') -- trim everything up to '<b>'
FROM TABLE (RegExp_Split_To_Table(t.ID, t.MHK_CONTENT, '</b>', 'i')
RETURNS (ID BIGINT, tok_num INTEGER, 
MHK_CONTENT VARCHAR(100) CHARACTER SET LATIN)) AS t   ```
---[Teradata Database] [9134] Input exceeds the buffer size limit.

Answer 1

根据您之前的问题扩展SQL，这似乎是您想要的:

with t as
(
select MHKAUDITLOGINTERNALID  as id, cast(MHK_CONTENT as clob) as MHK_CONTENT
from vcoreMEDHOK_MHK_Audit as ma
Where cast(ma.AUDITDATETIME as Date) >= '2021/08/15'
-- Where ma.AUDITDATETIME >= DATE '2021-08-15'
-- recommended to use DATE literals, the timestamp is here automatically converted to date  

)
select regexp_replace(Token, '<b>|</b>') -- remove tags
,dt.* 
FROM TABLE (RegExp_Split_To_Table(t.id, t.MHK_CONTENT,'<BR/> *', 'i') -- split whenever '<BR/>' occurs 
RETURNS ( id INT, TokenNum INT,
Token VARCHAR(32000) CHARACTER SET LATIN)
) AS dt                              

关于错误:Try to increase the size token size to the maximum.