Split CLOB to multiple VARCHAR2 columns by separator

Question

I have a table with columns:

f_name - Varchar
f_content - CLOB

table1

f_name     f_content
test1.txt  YL*1**50*1~
           RX*1~
           LR*2~
test2.txt  YL*1**49*1~
           EE*1~
           WW*2~

f_content is more than 4000 characters and sometimes over 10000 characters.

Is it possible to create 3 Varchar2 columns and split max of 4000 characters in each column till the most recent ~ so the entire CLOB is covered?

The output would be:

f_name     f_content       f_content_varchar1    f_content_varchar2    f_content_varchar3
test1.txt  YL*1**50*1~     YL*1**50*1~           RX*1~                  LR*2~
           RX*1~
           LR*2~
test2.txt  YL*1**49*1~      YL*1**49*1~         EE*1~                   WW*2~
           EE*1~
           WW*2~

Please NOTE - I want to split it into 3 columns BUT ONLY ending till last ~, so for example if a line ends ~ on character 4003, then it should not add the last line to varchar column and always consider the prior line ending with ~.

An example would be:

YL*1**50*1~
RX*1~
LR*2~

Let's say 4000 characters end on

"YL*1**50*1~
RX*1~
LR"

then in varchar column1, it should store:

"YL*1**50*1~
RX*1~"

and in varchar column2, it should store:

"LR*2~"

You can use SUBSTRING() method to split values from f_content for other 3 columns. first split from 0 to 4000 then 4001 to 8000 and finally 8001 to 12000. — mkRabbani
I want till the last ~ till 4000 and no extra characters, even if it is 3945 characters. From 3946 till ~ of 7946. — dang
I mean till the last ~. There would be many ~ in a clob. I just want till 4000. — dang

mkRabbani mkRabbani · Accepted Answer · 2019-05-05T10:26:45

If you can use CURSOR to manipulate multiple rows, this following script can help you by providing idea. Please noted that I considered end points on 20,40 and 60.

IMPORTANT: Please test this script on your test database first

IMPORTANT: Add necessary WHERE condition on SELECT statement. Other wise all records will be selected

DECLARE @NAME NVARCHAR(MAX)
DECLARE @F_NAME NVARCHAR(MAX)

DECLARE @CUT_1 INT
DECLARE @CUT_2 INT


DECLARE db_cursor CURSOR FOR 
SELECT f_name,f_content 
FROM your_table 
--WHERE Plese add conditions to pick only columns you wants to adjust 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @F_NAME,@NAME  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    DECLARE @A TABLE (POS INT)
    DECLARE @POS INT
    DECLARE @OLDPOS INT
    SELECT @OLDPOS=0        

    SELECT @POS=PATINDEX('%~%',@name) 
    WHILE @POS > 0 AND @OLDPOS <> @POS
     BEGIN
       INSERT INTO @A VALUES (@POS)
       SELECT @OLDPOS=@POS
       SELECT @POS=PATINDEX('%~%',SUBSTRING(@NAME,@POS + 1,LEN(@NAME))) + @POS
    END

    SELECT @CUT_1 = ISNULL(MAX([pos]),4000) from @a where pos <= 4000
    SELECT @CUT_2 = ISNULL(MAX([pos]),@CUT_1+4000) from @a where pos > @CUT_1 AND pos <= @CUT_1+4000


    UPDATE your_table
    SET [f_content_varchar1] = SUBSTRING(@Name,1,@CUT_1),
        [f_content_varchar2] = SUBSTRING(@Name,@CUT_1+1,@CUT_2-@CUT_1),
        [f_content_varchar3] = SUBSTRING(@Name,@CUT_2+1,12000-@CUT_2) 
    WHERE f_name = @F_NAME

    DELETE FROM @A
    SET @POS = NULL
    SET @OLDPOS = NULL
    SET @OLDPOS = NULL


    FETCH NEXT FROM db_cursor INTO  @F_NAME,@NAME 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

Split CLOB to multiple VARCHAR2 columns by separator

2 Answers