Search code examples
sqloracleoracle-sqldeveloper

Fetch sub-string from a column containing XML formatted String in oracle sql

As part of my query I am having one column which is having field value in two patterns as below

First Pattern:-

"First Name:<a class='text-lg text-info'> David Peter </a><br>
Deadline:<a class='text-lg text-info'>2019-12-07 20:05:01</a><br>
Remarks:<a class='text-lg text-info'>Some remarks with multiple spaces</a><br>"

Second Pattern:-

FirstName: <a class='text-lg text-info'>Alex Vander Veen</a><br>DeadLine: <a class='text-lg text-info'>2019-11-16 16:30:35</a>

I am trying to fetch out First Name/FirstName and Deadline value from these two fields.

Output needs to be as below :-

enter image description here

For getting this details case statement needs to be used on two patterns mentioned but I am really clueless on how to fetch only First Name/FirstName and Deadline from XML formatted string.

Solution

  • I am able to find solution to my above problem.

    SELECT SUBSTR(REGEXP_SUBSTR(A.FIELD_CONTENT,'>[^>]+<'),2,LENGTH(REGEXP_SUBSTR(A.FIELD_CONTENT,'>[^>]+<')) - 2 )  AS First_Name,
SUBSTR(SUBSTR(A.FIELD_CONTENT, REGEXP_INSTR(a.FIELD_CONTENT, '>', 1,4)+1),1, (REGEXP_INSTR(SUBSTR(A.FIELD_CONTENT, REGEXP_INSTR(a.FIELD_CONTENT, '>', 1,4)+1),'<',1,1) -1)) AS DEADLINE
FROM table_name1  a 
WHERE a.FIELD_CONTENT like '%First%'

    I came up with this. If anyone has any suggestion to make this less expensive. Suggestions are welcomed.. :-)