extract a substring from clob in oracle

Question

I have a clob with data

<?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><static-content language-id="en_US"><![CDATA[<script type="text/javascript">
function change_case()
{
    alert("here...");
    document.form1.type.value=document.form1.type.value.toLowerCase();
}
</script>

<form name=form1 method=post action=''''>
<input type=text name=type value=''Enter USER ID'' onBlur="change_case();">
<input type=submit value=Submit> </form>
</form>]]></static-content></root>

I want to extract the line with the onblur attribute, in this case:

<input type=text name=type value=''Enter USER ID'' onblur="change_case();">

Answer 1

Tom Kyte say how get varchar2 from clob in SQL or PL/SQL code

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:367980988799

And when you have varchar2 you can use SUBSTR or REGEXP_SUBSTR function for extract the line.

http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions147.htm#i87066

http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm

If you want to use SQL code, you can create this request

select col1, col2, func1(dbms_lob.substr( t.col_clob, 4000, 1 )) from table1 t

And in PL/SQL function "func1" you can do what you want with input string using SUBSTR or any other functions

Answer 2

Subdivide your problem. You want to extract a line of text from your CLOB which contains a particular substring. I can think of two possible interpretations of your requirements:

Option 1.

1. Split the CLOB into a series of lines - e.g. split it by newline/carriage return characters if that's really what you meant by "line".
2. Check each line to see if it includes the substring, e.g. onblur. If it does, you have found your line.

Option 2.

If you don't actually mean the line, but you want the <script>...</script> html fragment, you can use similar logic:

1. Search for the first occurrence of <script>.
2. Search for the next occurrence of </script> after that point. Extract the substring from <script> to </script>.
3. Search the substring for onblur. If it is found, return the substring. Otherwise, find the next occurrence of <script>, go to step 2, rinse, repeat.