oracle 从 CLOB 字段中提取 TEXT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29514092/
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 TEXT from a CLOB field
提问by CrazySpy
I have a CLOB field in my Oracle Database that store TEXT data in the following format:
我的 Oracle 数据库中有一个 CLOB 字段，它以以下格式存储 TEXT 数据：
__99__RU_LOCKED=N;;__99__RU_SUSPENDED=Y;;__17__USER_TYPE=A;;__17__USER_TYPE_610=A;;__17__GUIFLAG=0;;__17__DEFAULT_LANG_610=E;;__17__OUTPUT_DEVICE_46=LOCL;;__17__PRINT_IMMED=G;;__17__DELETE_AFTER_PRINT=D;;__17__CATT=*BLANK;;__17__CATT_46=*;;__17__DEC_FORMAT=*BLANK;;__17__DEC_FORMAT_46=X;;__17__DATE_FORMAT=2;;__17__PARAMETERS=OM_OBJM_NO_DISPLAYX;;__17__MEAS_EASLPFL=0;;__17__USER_GROUP=S1BR22;;__17__VALID_FROM=20080222;;__17__VALID_UNTIL=99991231;;__17__ACCOUNT=37004968;;
I'm using TOAD and while I am creating the query I can read the CLOB field with the following:
我正在使用 TOAD，在创建查询时，我可以使用以下内容读取 CLOB 字段：
--- To read the CLOB field.
select DBMS_LOB.substr(ADD_INFO_MASTER) from USER
This select return me the CLOB field HUMAN READABLE.
这个选择返回给我 CLOB 字段 HUMAN READABLE。
My question is: Is there any way to extract the one single value like ACCOUNT value from the line above? Keep in mind that this CLOB field can variate and the __17__ACCOUNT= will not be in the same place every time. I need a way to extract to locate the ;;__17__ACCOUNT= (this will be a pattern) and extract the the value 37004968.
我的问题是：有没有办法从上面的行中提取像 ACCOUNT 值这样的单个值？请记住，此 CLOB 字段可以变化，并且 __17__ACCOUNT= 不会每次都在同一位置。我需要一种提取方法来定位 ;;__17__ACCOUNT=（这将是一个模式）并提取值 37004968。
It is possible to achieve this while performing a query in TOAD?
在 TOAD 中执行查询时可以实现这一点吗？
回答by ThinkJet
If you want to deal with CLOB values larger than 4000 symbols length (Oracle 11g) or 32K length (Oracle 12c) then you must use DBMS_LOBpackage.
如果要处理大于 4000 个符号长度（Oracle 11g）或 32K 长度（Oracle 12c）的 CLOB 值，则必须使用DBMS_LOBpackage。
This package contains instr()and substr()functions which operates on LOBs.
这个软件包包含instr()和substr()其上的LOB操作的功能。
In your case query looks like that:
在您的情况下，查询如下所示：
with prm as (
select '__17__ACCOUNT' as fld_start from dual
)
select
dbms_lob.substr(
text,
-- length of substring
(
-- position of delimiter found after start of desired field
dbms_lob.instr(text, ';;', dbms_lob.instr(text, prm.fld_start))
-
-- position of the field description plus it's length
( dbms_lob.instr(text, prm.fld_start) + length(fld_start) + 1 )
),
-- start position of substring
dbms_lob.instr(text,prm.fld_start) + length(fld_start) + 1
)
from
text_table,
prm
Query above uses this setup:
上面的查询使用此设置：
create table text_table(text clob);
insert into text_table(text) values (
'__99__RU_LOCKED=N;;__99__RU_SUSPENDED=Y;;__17__USER_TYPE=A;;__17__USER_TYPE_610=A;;__17__GUIFLAG=0;;__17__DEFAULT_LANG_610=E;;__17__OUTPUT_DEVICE_46=LOCL;;__17__PRINT_IMMED=G;;__17__DELETE_AFTER_PRINT=D;;__17__CATT=*BLANK;;__17__CATT_46=*;;__17__DEC_FORMAT=*BLANK;;__17__DEC_FORMAT_46=X;;__17__DATE_FORMAT=2;;__17__PARAMETERS=OM_OBJM_NO_DISPLAYX;;__17__MEAS_EASLPFL=0;;__17__USER_GROUP=S1BR22;;__17__VALID_FROM=20080222;;__17__VALID_UNTIL=99991231;;__17__ACCOUNT=37004968;;'
);
For everyday use with development tools it may be useful to define a function which returns value of field with desired name and use it instead of writing complicated expressions each time.
E.g. :
对于开发工具的日常使用，定义一个函数来返回具有所需名称的字段值并使用它而不是每次都编写复杂的表达式可能会很有用。
例如：
create or replace function get_field_from_text(
pi_text in clob,
pi_field_name in varchar2
) return varchar2 deterministic parallel_enable
is
v_start_pos binary_integer;
v_field_start varchar2(4000);
v_field_value varchar2(32767);
begin
if( (pi_text is null) or (pi_field_name is null) ) then
return null;
end if;
v_field_start := pi_field_name || '=';
v_start_pos := dbms_lob.instr(pi_text, v_field_start);
if(v_start_pos is null) then
return null;
end if;
v_start_pos := v_start_pos + length(v_field_start);
v_field_value := dbms_lob.substr(
pi_text,
(dbms_lob.instr(pi_text, ';;', v_start_pos) - v_start_pos),
v_start_pos
);
return v_field_value;
end;
Usage:
用法：
select get_field_from_text(text,'__17__OUTPUT_DEVICE_46') from text_table
回答by pablomatico
You could use a regular expression to extract the value:
您可以使用正则表达式来提取值：
WITH your_table AS (
SELECT '__99__RU_LOCKED=N;;__99__RU_SUSPENDED=Y;;__17__USER_TYPE=A;;__17__USER_TYPE_610=A;;__17__GUIFLAG=0;;__17__DEFAULT_LANG_610=E;;__17__OUTPUT_DEVICE_46=LOCL;;__17__PRINT_IMMED=G;;__17__DELETE_AFTER_PRINT=D;;__17__CATT=*BLANK;;__17__CATT_46=*;;__17__DEC_FORMAT=*BLANK;;__17__DEC_FORMAT_46=X;;__17__DATE_FORMAT=2;;__17__PARAMETERS=OM_OBJM_NO_DISPLAYX;;__17__MEAS_EASLPFL=0;;__17__USER_GROUP=S1BR22;;__17__VALID_FROM=20080222;;__17__VALID_UNTIL=99991231;;__17__ACCOUNT=37004968;;' clob_field FROM DUAL
)
SELECT REGEXP_SUBSTR(clob_field,'__17__ACCOUNT=.*;;')
FROM your_table
Using that you would get "__17__ACCOUNT=37004968;;". You can easily extract the value with SUBSTR.
使用它你会得到“__17__ACCOUNT=37004968;;”。您可以使用 SUBSTR 轻松提取该值。
I think that in Oracle 11g REGEXP_SUBSTR has extra parameters that would let you extract a certain group within the regular expression.
我认为在 Oracle 11g REGEXP_SUBSTR 中有额外的参数，可以让您在正则表达式中提取某个组。
回答by AlexP
You can use INSTR and SUBSTR with CLOB datatype:
您可以将 INSTR 和 SUBSTR 与 CLOB 数据类型一起使用：
WITH T1 AS (
SELECT '__99__RU_LOCKED=N;;__99__RU_SUSPENDED=Y;;__17__USER_TYPE=A;;__17__USER_TYPE_610=A;;__17__GUIFLAG=0;;__17__DEFAULT_LANG_610=E;;__17__OUTPUT_DEVICE_46=LOCL;;__17__PRINT_IMMED=G;;__17__DELETE_AFTER_PRINT=D;;__17__CATT=*BLANK;;__17__CATT_46=*;;__17__DEC_FORMAT=*BLANK;;__17__DEC_FORMAT_46=X;;__17__DATE_FORMAT=2;;__17__PARAMETERS=OM_OBJM_NO_DISPLAYX;;__17__MEAS_EASLPFL=0;;__17__USER_GROUP=S1BR22;;__17__VALID_FROM=20080222;;__17__VALID_UNTIL=99991231;;__17__ACCOUNT=37004968;;' TEXT FROM DUAL
)
SELECT SUBSTR(TEXT,
INSTR(TEXT, '__17__ACCOUNT=') + LENGTH('__17__ACCOUNT') + 1, -- find the first position of the value
INSTR (TEXT, ';;', INSTR(TEXT, '__17__ACCOUNT=')) - (INSTR(TEXT, '__17__ACCOUNT=') + LENGTH('__17__ACCOUNT') + 1) -- length to read. Difference between the end position (the first ;; after your placeholder) and the value start position (the same value as above)
)
FROM T1;
However I like the REGEXP solution proposed by pablomatico more.
不过我更喜欢 pablomatico 提出的 REGEXP 解决方案。
