CODEWITHSUNDEEP
sqloracle-database
Baalback
Baalback

Reputation: 461

regexp_replace get specific character after single quotes and space

I am not that good in regex but I want to catch a specific word after Info_type, so the result would be DATABASE or APPLICATION or MOBILE .

example:

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"""

edit:

I have some other data like this :

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"""

I tried SELECT REGEXP_REPLACE(name, '(.*)(Info_type\=)') FROM TAB1

Upvotes: 0

Views: 67

Answers (1)

Joakim Danielson
Joakim Danielson

Reputation: 51973

Here is how to extract the word and I assume it is always surrounded by triple quotation marks

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

Update

This version is more flexible and allows for 1-3 quotation marks around the word

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

Update 2 Allowed for the word to contain space

Upvotes: 2

Related Questions