I have a table with the following structure:
table1
f_name f_content test1.txt |0002434299|354534|535345345|05|||BCV RESULT # 174|Test 12%| test2.txt |543566677|HTTYE|9w5w RRLL|05|||BBN RESULT # 144|Test 15#%|3
I need to separate f_content using pipes (|) and put appropriate position of the string.
The output table is:
f_name position value test1.txt 1 (null) test1.txt 2 0002434299 test1.txt 3 354534 test1.txt 4 535345345 test1.txt 5 05 test1.txt 6 (null) test1.txt 7 (null) test1.txt 8 BCV RESULT # 174 test1.txt 9 Test 12% test1.txt 10 (null) test2.txt 1 (null) test2.txt 2 543566677 test2.txt 3 HTTYE test2.txt 4 9w5w RRLL test2.txt 5 05 test2.txt 6 (null) test2.txt 7 (null) test2.txt 8 BBN RESULT # 144 test2.txt 9 Test 15#% test2.txt 10 3
I have over 500K records in table1. Each record has over 200 pipes.
Is there a way to write optimised query so that it can process 500K records with more than 200 pipes without filling the undo table space?
Can SQL query be written to process in chunks and keep inserting it in the output table?
Advertisement
Answer
You do not need (slow) regular expressions, and can do this with simple string functions:
Oracle Setup:
CREATE TABLE table1 ( f_name, f_content ) AS SELECT 'test1.txt', '|0002434299|354534|535345345|05|||BCV RESULT # 174|Test 12%|' FROM DUAL UNION ALL SELECT 'test2.txt', '|543566677|HTTYE|9w5w RRLL|05|||BBN RESULT # 144|Test 15#%|3' FROM DUAL CREATE TABLE output_table ( f_name VARCHAR2(20), position NUMBER(4,0), value VARCHAR2(50) );
Insert Statement:
INSERT INTO output_table ( f_name, position, value )
WITH rsqfc ( f_name, f_content, idx, spos, epos ) AS (
SELECT f_name, f_content, 1, 1, INSTR( f_content, '|', 1 )
FROM table1
UNION ALL
SELECT f_name, f_content, idx + 1, epos + 1, INSTR( f_content, '|', epos + 1 )
FROM rsqfc
WHERE epos > 0
)
SELECT f_name,
idx,
CASE
WHEN epos > 0
THEN SUBSTR( f_content, spos, epos - spos )
ELSE SUBSTR( f_content, spos )
END
FROM rsqfc
Output:
SELECT * FROM output_table ORDER BY f_name, position
F_NAME | POSITION | VALUE :-------- | -------: | :--------------- test1.txt | 1 | null test1.txt | 2 | 0002434299 test1.txt | 3 | 354534 test1.txt | 4 | 535345345 test1.txt | 5 | 05 test1.txt | 6 | null test1.txt | 7 | null test1.txt | 8 | BCV RESULT # 174 test1.txt | 9 | Test 12% test1.txt | 10 | null test2.txt | 1 | null test2.txt | 2 | 543566677 test2.txt | 3 | HTTYE test2.txt | 4 | 9w5w RRLL test2.txt | 5 | 05 test2.txt | 6 | null test2.txt | 7 | null test2.txt | 8 | BBN RESULT # 144 test2.txt | 9 | Test 15#% test2.txt | 10 | 3
db<>fiddle here