Oracle设置:
CREATE TABLE test_data ( value ) AS
SELECT 'Source: epw050015.catxtx.net
Description: vCenter: 198.11.12.98
alarm.NetworkConnectivityLostAlarm - Event: Lost Network Connectivity (11355590)
Summary: Lost network connectivity on virtual switch vSwitchiDRACvusb . Physical NIC vusb0 is down. Affected portgroups: iDRAC Network .
Date: 02/06/19 12:28:04
Host: epw050015.catxtx.net
Resource pool: W05P006 (6.5) GAD
Data center: W05' FROM DUAL
查询1 :
在文本内找到Host:
子字符串末尾的索引(在行的开头),然后找到后续换行符的索引,然后获取两者之间的子字符串:
SELECT TRIM( SUBSTR( value,start_host,end_host - start_host ) ) AS host
FROM (
SELECT value,INSTR( value,CHR(10)||'Host:' ) + 6 AS start_host,CHR(10),CHR(10)||'Host:' ) + 6 ) AS end_host
FROM test_data
)
查询2 :
或者您可以使用正则表达式,这可能是一个较慢的解决方案,但是代码更简单:
SELECT REGEXP_SUBSTR( value,'^Host:\s+(.*?)$',1,'m',1 ) AS host
FROM test_data
输出:
两个解决方案的输出:
| HOST |
| :------------------- |
| epw050015.catxtx.net |
db 提琴here
,
尝试一下:
SELECT TRIM(SUBSTR(COLUM_NAME,INSTR(COLUM_NAME,'Host:') + LENGTH('Host:'),'.net') - INSTR(COLUM_NAME,'Host:') - LENGTH('Host:') + LENGTH('.net'))) FROM TABLE_NAME
您可以在此处查看它的运行情况:http://sqlfiddle.com/#!4/9f207/4/0
这将找到Host:
的 position 和.net
的 position + length('。net')之间的字符串。然后TRIM
函数删除侧面的任何空格,因为您说过Host:
之后可能有多个空格
本文链接:https://www.f2er.com/3065526.html