从遵循模糊模式 SQL Netezza 的字符串中提取序列号 [英] extract serial from string that follow fuzzy pattern SQL Netezza
本文介绍了从遵循模糊模式 SQL Netezza 的字符串中提取序列号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请我有一条日志消息,其中包含一个模糊模式的序列,如下图所示
please I Have a log message that contains a serial with a fuzzy pattern that hard to follow like the below
|LogMsg |
|------------------------------------------------------------------------------------------|
|Customer Receive CPE Indoor. serial 21530369847SKA011094, user:ahmed.o.haraz |
|Customer Receive CPE Indoor as change. serial :21530369847SK9078291, user:Abdullah.M160275|
|Customer Receive CPE Indoor. serial:ZTERRT1H9202990 |
|Customer Receive CPE Indoor. serial 21530369847SKB333996 .UserName :TEDST.mohamed.badry |
|Customer Receive CPE Indoor as change. serial :21530373727skc298302, user:Frass.m195577 |
|Customer Receive CPE Indoor. serial 21530369847SKA267112 .UserName :seller.160002 |
我需要从如下所示的字符串中提取序列号
I need to extract the Serial from the string like the below
|Serial|
|21530369847SKA011094|
|21530369847SK9078291|
|ZTERRT1H9202990 |
|21530369847SKB333996|
|21530373727skc298302|
|21530369847SKA267112|
我使用 regexp_replace()
得到了提到的查询,但它错过了其中的一些
I got the mentioned query using regexp_replace()
but it missed some of them
select replace(replace(regexp_extract(logmsg, 'serial [^,]+'), 'serial ', ''), ':', '')
推荐答案
尝试
select get_value_varchar(
regexp_extract_all_sp(logmsg,
'(serial\s*:?)([^,\s]+)'),3) as serial
from serial;
它应该给你你正在寻找的结果
It should give you the result you are looking for
SERIAL
----------------------
21530369847SKA011094
21530369847SKA267112
21530369847SKB333996
21530369847SK9078291
ZTERRT1H9202990
21530373727skc298302
(6 rows)
这篇关于从遵循模糊模式 SQL Netezza 的字符串中提取序列号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文