Вопрос:
У меня есть поле CLOB в моей базе данных Oracle, в которой хранятся данные 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;;
Я использую TOAD, и пока я создаю запрос, я могу прочитать поле CLOB со следующим:
— To read the CLOB field. select DBMS_LOB.substr(ADD_INFO_MASTER) from USER
Этот select вернет мне поле CLOB ЧЕЛОВЕЧНОЕ ЧТЕНИЕ.
Мой вопрос: есть ли способ извлечь одно значение, например ACCOUNT, из строки выше? Имейте в виду, что это поле CLOB может изменяться, а __17__ACCOUNT = не будет в одном месте каждый раз. Мне нужно, чтобы извлечь, чтобы найти ;; __ 17__ACCOUNT = (это будет шаблон) и извлечь значение 37004968.
Этого можно достичь при выполнении запроса в TOAD?
Лучший ответ:
Если вы хотите иметь дело с значениями CLOB длиной более 4000 символов (Oracle 11g) или длиной 32K (Oracle 12c), вы должны использовать пакет DBMS_LOB.
Этот пакет содержит функции instr() и substr() которые работают с LOB.
В вашем случае запрос выглядит так:
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 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
В приведенном выше запросе используется эта настройка:
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;;’ );
Для повседневного использования с инструментами разработки может быть полезно определить функцию, которая возвращает значение поля с желаемым именем и использовать его вместо написания сложных выражений каждый раз.
Например:
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;
Применение:
select get_field_from_text(text,’__17__OUTPUT_DEVICE_46′) from text_table Ответ №1
Вы можете использовать регулярное выражение для извлечения значения:
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
Используя это, вы получите “__17__ACCOUNT = 37004968 ;;”. Вы можете легко извлечь значение с помощью SUBSTR.
Я думаю, что в Oracle 11g REGEXP_SUBSTR имеет дополнительные параметры, которые позволят вам извлечь определенную группу в регулярное выражение.
Ответ №2
Вы можете использовать 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;
Однако мне нравится решение REGEXP, предложенное pablomatico больше.
