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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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 Go to previous message
Solomon Yakobson
Messages: 2710
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.

Previous Topic: Purging old records
Next Topic: Bug in Oracle External Table Not display Decimal Values
Goto Forum:
  


Current Time: Sat Feb 24 09:14:49 CST 2018

Total time taken to generate the page: 0.02684 seconds