This discussion is archived
11 Replies Latest reply: Aug 2, 2013 8:01 AM by 795014 RSS

Help on a BLOB column

795014 Newbie
Currently Being Moderated


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 Newbie
    Currently Being Moderated

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

  • 2. Re: Help on a BLOB column
    Greg.Spall Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

     

    Thanks

  • 5. Re: Help on a BLOB column
    AlbertoFaenza Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    *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 Expert
    Currently Being Moderated

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

  • 10. Re: Help on a BLOB column
    795014 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points