CODEWITHSUNDEEP
sqlregexoracle-databaseplsqlsubstr
John Doe
John Doe

Reputation: 71

Extract a substring from a field PLSQL

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

Upvotes: 0

Views: 779

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I would be tempted to use regexp_replace():

select regexp_replace(sprcmnt_text_nar, '^.*fechaAutenticacion="([^"])".*$', '\1')

Upvotes: 0

GMB
GMB

Reputation: 222682

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.

Upvotes: 1

Related Questions