Home » SQL & PL/SQL » SQL & PL/SQL » Split Blog column to multiple columns within a row
Split Blog column to multiple columns within a row [message #657049] |
Wed, 26 October 2016 22:20 |
alexust
Messages: 1 Registered: April 2007
|
Junior Member |
|
|
Dear All,
I have a column in blob format. I use the function to convert to text
select utl_raw.cast_to_varchar2(dbms_lob.substr(P_COL)) from P_TABLE
But the content of P_COL is in json format. which mean the some of data are shown in optional way. if the data has no value, it will not shown.
I am using 11G, there is no native json library. Any implement can extract them in a row.
like
account id1 id2 id3 amount num
--------------------------------------------------------
100 D6557 103.00 003
110 D12345 D12300 V023993 103.00 003
120 D6558 103.00 003
130 D6557 103.00 003
TABLE P_TABLE (P_COL BLOB)
row 1 {"account":"100","id1":"D12345","amount":103.00,"Num":"003"}
row 2 {"account":"110","id1":"D12340", "id2":"D12300", "id3":"V023993","amount":103.00,"Num":"003"}
row 3 {"account":"120","id3":"D6558","amount":103.00,"Num":"003"}
row 4 {"account":"130","id2":"D6557","amount":103.00,"Num":"003"}
thanks help
|
|
|
Re: Split Blog column to multiple columns within a row [message #657054 is a reply to message #657049] |
Thu, 27 October 2016 02:36 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
In 12c you have native JSON functions, before you have to analyze the string by yourself.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data.
Now that you have converted your BLOB to string then use INSTR and SUBSTR to split the string into columns you want:
SQL> col account format a7
SQL> col id1 format a7
SQL> col id2 format a7
SQL> col id3 format a7
SQL> col amount format a6
SQL> col num format a3
SQL> with
2 data as ( -- Just to ease the SUBSTR below
3 select replace(val, '}', ',') val from t
4 )
5 select decode(instr(val, '"account":'),
6 0, '',
7 trim(both '"' from
8 substr(val,
9 instr(val, '"account":') + length('"account":'),
10 instr(val, ',', instr(val, '"account":')
11 + length('"account":'))
12 - instr(val, '"account":') - length('"account":')
13 - 1)))
14 account,
15 decode(instr(val, '"id1":'),
16 0, '',
17 trim(both '"' from
18 substr(val,
19 instr(val, '"id1":') + length('"id1":'),
20 instr(val, ',', instr(val, '"id1":')
21 + length('"id1":'))
22 - instr(val, '"id1":') - length('"id1":')
23 - 1)))
24 id1,
25 decode(instr(val, '"id2":'),
26 0, '',
27 trim(both '"' from
28 substr(val,
29 instr(val, '"id2":') + length('"id2":'),
30 instr(val, ',', instr(val, '"id2":')
31 + length('"id2":'))
32 - instr(val, '"id2":') - length('"id2":')
33 - 1)))
34 id2,
35 decode(instr(val, '"id3":'),
36 0, '',
37 trim(both '"' from
38 substr(val,
39 instr(val, '"id3":') + length('"id3":'),
40 instr(val, ',', instr(val, '"id3":')
41 + length('"id3":'))
42 - instr(val, '"id3":') - length('"id3":')
43 - 1)))
44 id3,
45 decode(instr(val, '"amount":'),
46 0, '',
47 trim(both '"' from
48 substr(val,
49 instr(val, '"amount":') + length('"amount":'),
50 instr(val, ',', instr(val, '"amount":')
51 + length('"amount":'))
52 - instr(val, '"amount":') - length('"amount":')
53 - 1)))
54 amount,
55 decode(instr(val, '"Num":'),
56 0, '',
57 trim(both '"' from
58 substr(val,
59 instr(val, '"Num":') + length('"Num":'),
60 instr(val, ',', instr(val, '"Num":')
61 + length('"Num":'))
62 - instr(val, '"Num":') - length('"Num":')
63 - 1)))
64 Num
65 from data
66 /
ACCOUNT ID1 ID2 ID3 AMOUNT NUM
------- ------- ------- ------- ------ ---
100 D12345 103.0 003
110 D12340 D12300 V023993 103.0 003
120 D6558 103.0 003
130 D6557 103.0 003
4 rows selected.
|
|
|
Re: Split Blog column to multiple columns within a row [message #657093 is a reply to message #657049] |
Thu, 27 October 2016 14:15 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Storing text in BLOB doesn't make much sense (unless you want to preserve character set). I'll assume it is CLOB. Also, you didn't post Oracle version. I'll assume you are on 12C:
SQL> SELECT *
2 FROM P_TABLE
3 /
P_COL
--------------------------------------------------------------------------------
{"account":"100","id1":"D12345","amount":103.00,"Num":"003"}
{"account":"110","id1":"D12340", "id2":"D12300", "id3":"V023993","amount":103.00
{"account":"120","id3":"D6558","amount":103.00,"Num":"003"}
{"account":"130","id2":"D6557","amount":103.00,"Num":"003"}
SQL> SELECT J.*
2 FROM P_TABLE,
3 JSON_TABLE(
4 P_COL,'$'
5 COLUMNS
6 (
7 ACCOUNT VARCHAR2(10 CHAR) PATH '$.account',
8 ID1 VARCHAR2(10 CHAR) PATH '$.id1',
9 ID2 VARCHAR2(10 CHAR) PATH '$.id2',
10 ID3 VARCHAR2(10 CHAR) PATH '$.id3',
11 AMOUNT VARCHAR2(10 CHAR) PATH '$.amount',
12 NUM VARCHAR2(10 CHAR) PATH '$.Num'
13 )
14 ) J
15 /
ACCOUNT ID1 ID2 ID3 AMOUNT NUM
---------- ---------- ---------- ---------- ---------- ----------
100 D12345 103.00 003
110 D12340 D12300 V023993 103.00 003
120 D6558 103.00 003
130 D6557 103.00 003
SQL>
SY.
|
|
|
Re: Split Blog column to multiple columns within a row [message #657095 is a reply to message #657093] |
Thu, 27 October 2016 14:43 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
And if you are on lower version you can download PLJSON and do something like:
SQL> DECLARE
2 V_JSON JSON;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(
5 RPAD('account',10) ||
6 RPAD('id1',10) ||
7 RPAD('id2',10) ||
8 RPAD('id3',10) ||
9 RPAD('amount',10) ||
10 RPAD('Num',10)
11 );
12 DBMS_OUTPUT.PUT_LINE(
13 '--------- ' ||
14 '--------- ' ||
15 '--------- ' ||
16 '--------- ' ||
17 '--------- ' ||
18 '--------- '
19 );
20 FOR V_REC IN (SELECT * FROM P_TABLE) LOOP
21 V_JSON := JSON(V_REC.P_COL);
22 DBMS_OUTPUT.PUT_LINE(
23 RPAD(V_JSON.GET('account').get_string(),10) ||
24 CASE
25 WHEN V_JSON.GET('id1') IS NULL THEN ' '
26 ELSE RPAD(V_JSON.GET('id1').get_string(),10)
27 END ||
28 CASE
29 WHEN V_JSON.GET('id2') IS NULL THEN ' '
30 ELSE RPAD(V_JSON.GET('id2').get_string(),10)
31 END ||
32 CASE
33 WHEN V_JSON.GET('id3') IS NULL THEN ' '
34 ELSE RPAD(V_JSON.GET('id3').get_string(),10)
35 END ||
36 RPAD(V_JSON.GET('amount').get_number(),10) ||
37 RPAD(V_JSON.GET('Num').get_string(),10)
38 );
39 END LOOP;
40 END;
41 /
account id1 id2 id3 amount Num
--------- --------- --------- --------- --------- ---------
100 D12345 103 003
110 D12340 D12300 V023993 103 003
120 D6558 103 003
130 D6557 103 003
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
Re: Split Blog column to multiple columns within a row [message #657096 is a reply to message #657049] |
Thu, 27 October 2016 15:01 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
REGEXP solution:
SQL> SELECT REGEXP_SUBSTR(P_COL,'"account":"(.*?)"',1,1,null,1) account,
2 REGEXP_SUBSTR(P_COL,'"id1":"(.*?)"',1,1,null,1) id1,
3 REGEXP_SUBSTR(P_COL,'"id2":"(.*?)"',1,1,null,1) id2,
4 REGEXP_SUBSTR(P_COL,'"id3":"(.*?)"',1,1,null,1) id3,
5 REGEXP_SUBSTR(P_COL,'"amount":([^,]*)',1,1,null,1) amount,
6 REGEXP_SUBSTR(P_COL,'"Num":"(.*?)"',1,1,null,1) num
7 FROM P_TABLE
8 /
ACCOUNT ID1 ID2 ID3 AMOUNT NUM
---------- ---------- ---------- ---------- ---------- ----------
100 D12345 103.00 003
110 D12340 D12300 V023993 103.00 003
120 D6558 103.00 003
130 D6557 103.00 003
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:13:06 CDT 2024
|