database 从 oracle 中的 clob 中提取子字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10129998/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
extract a substring from clob in oracle
提问by micheal marquiz
I have a clob with data
我有一个带有数据的clob
<?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:
我想用 onblur 属性提取行，在这种情况下：
<input type=text name=type value=''Enter USER ID'' onblur="change_case();">
回答by Alexander
Tom Kyte say how get varchar2 from clob in SQL or PL/SQL code
Tom Kyte 说如何在 SQL 或 PL/SQL 代码中从 clob 中获取 varchar2
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:367980988799
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.
当你有 varchar2 时，你可以使用 SUBSTR 或 REGEXP_SUBSTR 函数来提取行。
http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions147.htm#i87066
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
http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm
If you want to use SQL code, you can create this request
如果要使用 SQL 代码，可以创建此请求
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
在 PL/SQL 函数“func1”中，您可以使用 SUBSTR 或任何其他函数对输入字符串执行您想要的操作
回答by Jeffrey Kemp
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:
细分您的问题。您想从包含特定子字符串的 CLOB 中提取一行文本。我可以想到对您的要求的两种可能的解释：
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".
- Check each line to see if it includes the substring, e.g.
onblur. If it does, you have found your line.
- 将 CLOB 拆分为一系列行 - 例如，如果这确实是您所说的“行”的意思，则用换行符/回车符将其拆分。
- 检查每一行以查看它是否包含子字符串，例如
onblur. 如果是这样，您已经找到了您的线路。
Option 2.
选项 2。
If you don't actually mean the line, but you want the <script>...</script>html fragment, you can use similar logic:
如果您实际上不是指line，而是想要<script>...</script>html 片段，则可以使用类似的逻辑：
- Search for the first occurrence of
<script>. - Search for the next occurrence of
</script>after that point. Extract the substring from<script>to</script>. - 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.
- 搜索第一次出现的
<script>。 - 搜索
</script>该点之后的下一次出现。从<script>to 中提取子字符串</script>。 - 在子字符串中搜索
onblur。如果找到，则返回子字符串。否则，找到下一个<script>，转到步骤 2，冲洗，重复。
