Search code examples
sqloracle-databaseclobregexp-replace

How to replace TAB char in a CLOB column?

I've been searching a lot for a solution but found none.

My simple question: I have a Oracle12.2 table with a CLOB column, here and there are TABS, how to remove them in my sqlplus queries?

Below instructions work well

dbms_lob.substr(L80T2.tridata, 3, 130) ||';'||
dbms_lob.substr(L80T2.tridata, 9, 133) ||';'||
dbms_lob.substr(L80T2.tridata, 20, 161),

But when I try to remove the tabs with i.e.

regexp_replace('dbms_lob.substr(L80T2.tridata, 20, 161)', chr(9), ' ',),

I get errors.

Any help is very much appreciated.

Here is my query:

select
dbms_lob.substr(L80T2.tridata, 9, 91) ||';'||
dbms_lob.substr(L80T2.tridata, 4, 60) ||';'||
dbms_lob.substr(L80T2.tridata, 24, 100) ||';'||
dbms_lob.substr(L80T2.tridata, 3, 130) ||';'||
dbms_lob.substr(L80T2.tridata, 9, 133) ||';'||
dbms_lob.substr(L80T2.tridata, 20, 161) ||';'||                 
L79T1.trno ||';'|| L79T1.datereg ||';'|| L79T1.trtype ||';'|| L79T1.trstat
from L79T1  
inner join L80T2 on (L80T2.trno = L79T1.trno)
where L79T1.trtype = '518' AND
    L79T1.datereg >= trunc(sysdate) -13 AND
    L79T1.datereg < trunc(sysdate);

And here is the ouput:

P0042842 ;2000;011112G016V3AN0033      ;P02;000065186;25674XX             ;295428109;2020-12-11 15:05:07;518;30
P0042883 ;2000;013494G010QVAN0033      ;P02;000037752;26545XX             ;295428110;2020-12-11 15:05:08;518;30
P0042485 ;2000;011112G016V3AN0033      ;P02;000050610;25668XX             ;295428112;2020-12-11 15:05:09;518;30

Tabs have been lost in the copy/paste, sorry.

Could it be on the sqlplus output formatting parameters I use ?

SET FEEDBACK OFF
SET ECHO OFF
SET FEED OFF
SET VERIFY OFF
SET HEADING OFF
SET NEWPAGE NONE
SET LINESIZE 300
SET TRIMSPOOL ON
SET BUFFER 10000
Solution

  • Adding to Barbaros' answer, it sounds like you want to remove whitespace (EITHER tabs OR trailing spaces) from your fields. In that case, I'd suggest adding RTRIM, to only remove trailing spaces from the right side:

    select
dbms_lob.substr(L80T2.tridata, 9, 91) ||';'||
dbms_lob.substr(L80T2.tridata, 4, 60) ||';'||
RTRIM(REPLACE(dbms_lob.substr(L80T2.tridata, 24, 100), CHR(9))) ||';'||
dbms_lob.substr(L80T2.tridata, 3, 130) ||';'||
dbms_lob.substr(L80T2.tridata, 9, 133) ||';'||
RTRIM(REPLACE(dbms_lob.substr(L80T2.tridata, 20, 161), CHR(9))) ||';'||        
...

    If you know you only have spaces and no tabs, you can just use RTRIM alone.

    Regexp_replace is a bit slower, but you can do something similar with it - to remove trailing whitespace:

    regexp_replace(dbms_lob.substr(L80T2.tridata, 24, 100),'[[:space:]]+$') ||';'||