How to extract values from a text CLOB field in SQL

Question

I have an SQL table column (BINARYTEXT) populated with a CLOB. Within this CLOB is a number of attributes, e.g.,

CE.EffDate="20140106";
CE.CCY="EUR";
CE.TransactionType="STANDARDEUROPEAN";
CE.CAL="LON";

I need to extract only the value of the CE.TransactionType attribute contained between the double quotes so 'STANDARDEUROPEAN'. Note that the CLOB does not contain XML and only contains attributes as above with no start or end tags.

I have worked out how to do this using the REGEXP_SUBSTR function when I specify the string in the command:

select REGEXP_SUBSTR('CE.TransactionType="STANDARDEUROPEAN"', '="[^"]+') transtype
from DUAL

which returns: ="STANDARDEUROPEAN

I am unable to manipulate this into using the CLOB as the string. This does not work:

select REGEXP_SUBSTR(BINARYTEXT,'CE.TransactionType="STANDARDEUROPEAN"', '="[^"]+')  transtype
from DUAL

This comes up with a blank integer error, although I am unsure why it is expecting an integer.

Answer 1

You can use the DBMS_LOB package to extract the string.

SELECT REGEXP_SUBSTR(DBMS_LOB.substr(BINARYTEXT,3000) ,'CE.TransactionType="STANDARDEUROPEAN"', '="[^"]+')  transtype
FROM YOURTABLENAME

The only thing you might have problems with is the buffer size, which is restricted to 32767 bytes (it's set to 3000 in my example). If your data is bigger than this, then you'd need to query it in chunks. See the documentation here

If you need to change the TransactionType you're searching for, you should probably create a function which takes that part of the string as a parameter and build the SQL content dynamically.

Answer 2

Use:

select BINARYTEXT from your_table
where regexp_like(dat,'^(\CE.TransactionType=)?"[^"]+"?;$');

Answer 3

You can try the below SQL:

select substr ( REGEXP_SUBSTR(to_clob ('CE.EffDate="20140106"; CE.CCY="EUR";CE.TransactionType="STANDARDEUROPEAN";CE.CAL="LON";'), '="[^"]+', 1,3), 3)  transtype from DUAL

Answer 4

If your db does not have REGEXP, you can use the DBMS_LOB package (as mentioned by StevieG). Here is SQL using just the DBMS_LOB... I used the semicolon as a delimiter as it is in your CLOB string in the question.

WITH
    table_test As
        (
            Select
              TO_CLOB('... ... ...
                       Some other text within the CLOB field
                       ...
                       CE.EffDate="20140106";
                       CE.CCY="EUR";
                       CE.TransactionType="STANDARDEUROPEAN";
                       CE.CAL="LON";
                       and yet some more text;
                       and more ...
                       ... ... ...
                    ') "BINARYTEXT"
            From Dual
        )
Select
    DBMS_LOB.SubStr(
                      DBMS_LOB.SubStr(BINARYTEXT, 100, InStr(BINARYTEXT, 'CE.TransactionType=') + Length('CE.TransactionType=')+1),
                      InStr(DBMS_LOB.SubStr(BINARYTEXT, 100, InStr(BINARYTEXT, 'CE.TransactionType=') + Length('CE.TransactionType=')+1), ';')-2,
                      1

                    ) "TRANSACTION_TYPE"
From
    table_test
--
-- Result:
--
-- TRANSACTION_TYPE
-- STANDARDEUROPEAN

Answer 5

This is because the REGEXP_SUBSTR function expects a string as its first argument, and the BINARYTEXT column is a CLOB. To fix this, you can use the DBMS_LOB package to convert the CLOB to a string. The following query will work:

select REGEXP_SUBSTR(DBMS_LOB.substr(BINARYTEXT, 3000), 'CE.TransactionType="STANDARDEUROPEAN"', '="[^"]+')  transtype 
from DUAL

This query will first use the DBMS_LOB.substr function to convert the BINARYTEXT column to a string with a maximum length of 3000 characters. It will then use the REGEXP_SUBSTR function to extract the value of the CE.TransactionType attribute from the string. The result will be a string containing the value of the CE.TransactionType attribute, or NULL if the attribute is not found.

Here is an example of the output of this query:

| transtype |
|----------|
| STANDARDEUROPEAN |