11 Replies Latest reply: Aug 2, 2013 10:01 AM by 795014 RSS

    Help on a BLOB column

    795014


      Hi Everyone,

      I need your help on this query. I need to get a particular value from a column which is a BLOB datatype. Here is the sample data

       

      ID                           TESTDATA

      1                            Best Buy 00001234 12222 30 00

      2                            Lowes 00001234 12222 100 00

      3                            Walmart 00001234 12222 129 00

      4                            abc 00001234 12222 5000 00

      5                            Toshiba 00001234 12222 21 00

      6                            abcdefghij 00001234 12222 49 00

       

      Where '00001234' is the Invoice , '12222' is Netamount field and highlighted in red is the $ amount for that invoice. The only data i need query to return 12222 (netamount)= $amount. I tried using substr

       

      select substr((TESTDATA),19,26)test from TABLEA;

       

      But this gives me the only the first row but not other amount which have different positions. Here is the desired output

      Output

      ID                            TESTDATA

      1                           12222 30

      2                            12222 100

      3                            12222 129

      4                            12222 5000

      5                            12222 21

      6                            12222 49 

       

      Any Help on this is really appreciate

       

      Thanks

        • 1. Re: Help on a BLOB column
          SGUN

          Can you send your table DML and insert statement for your sample data?

          • 2. Re: Help on a BLOB column
            Greg Spall

            Practicing my reg-exp-fu

             

            Not sure if there's a better way, but I got this:

             

            with xx as (
                     select 1 id, 'Best Buy 00001234 12222 30 00' testdata from dual union all
                     select 2 id, 'Lowes 00001234 12222 100 00'          from dual union all
                     select 3 id, 'Walmart 00001234 12222 129 00'          from dual union all
                     select 4 id, 'abc 00001234 12222 5000 00'          from dual union all
                     select 5 id, 'Toshiba 00001234 12222 21 00'          from dual union all
                     select 6 id, 'abcdefghij 00001234 12222 49 00'          from dual
                  )
            Select id, testdata,
                   regexp_replace ( testdata, '^(.*?)(\d+) (\d+) (\d+) (\d+)$',
                                          '\3 \4')  amt
               from xx;
            

             

                    ID TESTDATA                        AMT

            ---------- ------------------------------- ----------------------------------------

                     1 Best Buy 00001234 12222 30 00   12222 30

                     2 Lowes 00001234 12222 100 00     12222 100

                     3 Walmart 00001234 12222 129 00   12222 129

                     4 abc 00001234 12222 5000 00      12222 5000

                     5 Toshiba 00001234 12222 21 00    12222 21

                     6 abcdefghij 00001234 12222 49 00 12222 49

            • 3. Re: Help on a BLOB column
              Etbin

              Maybe

               

              with

              the_clob as

              (select to_clob('ID                           TESTDATA' || chr(10) ||

                              '1                            Best Buy 00001234 12222 30 00' || chr(10) ||

                              '2                            Lowes 00001234 12222 100 00' || chr(10) ||

                              '3                            Walmart 00001234 12222 129 00' || chr(10) ||

                              '4                            abc 00001234 12222 5000 00' || chr(10) ||

                              '5                            Toshiba 00001234 12222 21 00' || chr(10) ||

                              '6                            abcdefghij 00001234 12222 49 00' || chr(10)

                             ) the_column

                  from dual

              )

              select to_number(substr(the_line,1,instr(the_line,' ') - 1)) the_id,

                     12222 the_netamount,

                     to_number(substr(the_line,

                                      instr(the_line,' ',-1,2) + 1,

                                      instr(the_line,' ',-1,1) - instr(the_line,' ',-1,2) - 1

                                     )

                              ) the_amount

                from (select substr(the_column,

                                    instr(the_column,chr(10),1,level) + 1,

                                    instr(the_column,chr(10),1,level + 1) - instr(the_column,chr(10),1,level) - 1

                                   ) the_line

                        from the_clob

                      connect by level < length(the_column) - length(replace(the_column,chr(10)))

                     )

               

              Regards

               

              Etbin

              • 4. Re: Help on a BLOB column
                795014

                What if you have 1000 rows then I cannot doing union all right?

                 

                Thanks

                • 5. Re: Help on a BLOB column
                  AlbertoFaenza

                  Hi,

                   

                  you can use regular expression to extract the data you want by specifying the recurrence of a digit in your text:

                   

                  with xx as

                  (

                     select 1 id, 'Best Buy 00001234 12222 30 00' testdata from dual union all

                     select 2 id, 'Lowes 00001234 12222 100 00'            from dual union all

                     select 3 id, 'Walmart 00001234 12222 129 00'          from dual union all

                     select 4 id, 'abc 00001234 12222 5000 00'             from dual union all

                     select 5 id, 'Toshiba 00001234 12222 21 00'           from dual union all

                     select 6 id, 'abcdefghij 00001234 12222 49 00'        from dual

                  )

                  select id, testdata

                       , regexp_substr ( testdata, '\d+',1,1) invoice

                       , regexp_substr ( testdata, '\d+',1,2) netamt

                       , regexp_substr ( testdata, '\d+',1,3) amt$

                    from xx;

                   

                   

                   

                          ID TESTDATA                        INVOICE         NETAMT          AMT$          

                  ---------- ------------------------------- --------------- --------------- ---------------

                           1 Best Buy 00001234 12222 30 00   00001234        12222           30            

                           2 Lowes 00001234 12222 100 00     00001234        12222           100           

                           3 Walmart 00001234 12222 129 00   00001234        12222           129           

                           4 abc 00001234 12222 5000 00      00001234        12222           5000          

                           5 Toshiba 00001234 12222 21 00    00001234        12222           21            

                           6 abcdefghij 00001234 12222 49 00 00001234        12222           49   

                   

                  Regards.

                  Al

                  • 6. Re: Help on a BLOB column
                    795014

                    Hi AI,

                    Here is the data I have in the BLOB column. All these data is in one line. So in this case its very diffucult to find the position. I need the 120001 (netamount) = 20 ($amount) and the position of 120001 20 is changed in rest of the data

                     

                    "0 13521 5 0 "13-JAN-12"0 2365 5 0 "Invoice"0 16 7 0 0.0.0.1 /bill 100000 1 0 126 9 0 1 793 5 0 "234-456789" 1 215 14 0 01 788 14 0 01 786 14 0 01 800 14 0 01 790 14 0 01 13525 5 0 "01-Dec-96"0 485 9 0 1 499 5 0 "Percentage %1 discount due to Early Payment."0 485 9 1 1 499 5 0 "^ Precent Charges"0 1678 9 0 1 106 5 0 "0000112345"1 40 7 0 0.0.0.1 /account 3456789 197

                    1 1505 5 0 1 109 5 0 "nl"1 17 5 0 "USD"1 13522 9 1 2 158 5 0 ""2 166 5 0 "ABDC "2 169 5 0 "2013 AB"2 167 5 0 "JOHN"2 170 5 0 "DC"0 13512 9 0 1 13518 14 0 01 13517 14 0 00 120001 20 0 00 788 14 0 00 918 3 0 0

                     

                    Thanks

                    • 7. Re: Help on a BLOB column
                      AlbertoFaenza

                      Hi,

                       

                      Is all amount of data just related to one record? That's not ideal to extract information.

                      Do you have a description of the data?

                      Where did you get this information from?

                      Maybe you should review the way you get this information.

                       

                      Regards.

                      Al

                      • 8. Re: Help on a BLOB column
                        Greg Spall

                        *facepalm*

                         

                        I used those union alls because YOU DIDN'T PROVIDE A CREATE TABLE.

                        So I find that easier to manufacture YOUR test case.

                         

                        *sigh*

                        Replace the entire "with xx as ( ... whatever .. )"

                        with your table.

                         

                        xx is just me reproducing YOUR table.

                        • 9. Re: Help on a BLOB column
                          Greg Spall

                          Oh that's slick .. I kinda learn that I'm still brute forcing with regexp_replace

                          • 10. Re: Help on a BLOB column
                            795014

                            Thank You AI. I used ur query as a reference and worked my way to get close to the output I want. Here is the query i wrote

                             

                            select id,substr(blob_to_clob(testdata),instr (blob_to_clob(testdata), 120001, 1),9) netamount

                            from TABLEA where obj_id0 = 4;

                             

                            Output:

                             

                            ID          NETAMOUNT

                            4            120001 20

                             

                            Now I am trying to split 120001 20 into two columns

                             

                            netamount    $amount

                            120001            20

                             

                            Hope I get this done

                            • 11. Re: Help on a BLOB column
                              795014

                              I got this resolved. Pasting the query to help others

                               

                              select id,REGEXP_SUBSTR(substr(blob_to_clob(testdata),instr (blob_to_clob(testdata), 120001, 1),9),'[^ ]+') Net_Amount,

                              REGEXP_SUBSTR(substr(blob_to_clob(testdata),instr (blob_to_clob(testdata), 120001, 1),9),'[^ ]+',1,2) Amount

                              from TABLEA where obj_id0 = 4;

                               

                              Thanks