I just start with PL/SQL and I need to extract certain information from a field where I have stored all XML text with approximately 4,000 characters, if not more, consulting the respective documentation I see that for what I need you can use the function SUBSTR or the function I REGEXP_SUBSTR do not know which of the two can be used in my case.
From all the text that I have stored in the field I need to extract exactly one specific data, the following is only a part of the text that I have in the field.
fechaAutenticacion="2019-10-17T17:14:23"
From this part of the text I need to extract what it contains fechaAutenticacion, I mean this part 2019-10-17T17:14:23 without its quotes and then display it in a new column.
The following is the query to the table and the field where I have all the text with more than 4,000 characters.
SELECT SPRCMNT_TEXT_NAR
FROM SPRCMNT
Assuming that pattern ''fechaAutenticacion="<DATE>"' consistently indicates the part that you want to trap, you can use regexp_substr() like so:
select regexp_substr(sprcmnt_text_nar, 'fechaAutenticacion="([^"]+)"', 1, 1, null, 1) authentication_date
from sprcmnt
The parentheses define a capturing group within the matching part of the string; then, the last argument tells Oracle to return the first (only) captured group rather than the all match.