regexp_replace在单引号和空格后获取特定字符

Question

我不太擅长regex，但我想在Info_type之后捕获一个特定的单词，所以结果会是DATABASE、APPLICATION或MOBILE。

示例：

Flyfast,unix.system,1-1-1,""Table X"" D-Day=""Flood"" id =123123PTIWQ Type='A' info_name=""Fast""  Info_type="""DATABASE""" Starting="10:00:10" Ending=""0000"" Comments="""NONE"""
Flyfast,unix.system,1-1-1,""Table X"" D-Day=""Flood"" id =123123PTIWQ Type='A' info_name=""Fast""  Info_type="""APPLICATION""" Starting="07:00:30" Ending=""0000"" Comments="""NONE"""
Flyfast,unix.system,1-1-1,""Table X"" D-Day=""Flood"" id =123123PTIWQ Type='A' info_name=""Fast""  Info_type="""MOBILE""" Starting="02:00:20" Ending=""0000"" Comments="""NONE"""
Flyfast,unix.system,1-1-1,""Table X"" D-Day=""Flood"" id =123123PTIWQ Type='A' info_name=""Fast""  Info_type="""DATABASE""" Starting="00:00:10" Ending=""0000"" Comments="""NONE"""

编辑：

我还有一些其他数据，比如：

Flyfast,unix.system,1-1-1,""Table X"" D-Day=""Flood"" id =123123PTIWQ Type='A' info_name=""Fast""  Info_type="""DATABASE A""" Starting="00:00:10" Ending=""0000"" Comments="""NONE"""

我试过SELECT REGEXP_REPLACE(name, '(.*)(Info_type=)') FROM TAB1

Answer 1

下面是如何提取单词的，我假设它总是被三个引号包围

SELECT REGEXP_REPLACE(name, '^.*Info_type="""([A-Z ]*)""".*', '1') 
FROM tab1

更新

这个版本更灵活，允许在单词周围使用1-3个引号

SELECT REGEXP_REPLACE(name, '^.*Info_type=["]{1,3}([A-Z ]*)["]{1,3}.*', '1') 
FROM tab1

更新2允许单词包含空格