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~"

2 Answers

0
mkRabbani On

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 @[email protected]
       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,@[email protected]_1),
        [f_content_varchar3] = SUBSTRING(@Name,@CUT_2+1,[email protected]_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 
0
Gordon Linoff On

If you only care about the first 4000 characters, then convert the value to a varchar2() and then use regular expressions:

select t1.*,
       regexp_substr(f_content_str, '[^~]+[~]', 1, 1) as part1,
       regexp_substr(f_content_str, '[^~]+[~]', 1, 2) as part2,
       regexp_substr(f_content_str, '[^~]+[~]', 1, 3) as part3
from (select t1.*,
             dbms_lob.substr(t1.f_content, 4000, 1)  as f_content_str
      from t
     ) t;