0
There are other ways without use Regex to extract data of a text column in Oracle 19c.
So far I managed using Regex as follows:
select REGEXP_SUBSTR(p.observacao, '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]') as data
from pessoa p
Let’s say that my observation column has the following information: 'test 24/08/2020 abc'. Soon the expected result is: 24/08/2020.
Some references I researched:
1
Yes, it is possible. As long as you know which form the date is.
See, first find the data that matches your pattern using a filter LIKE.
campo LIKE '%__/__/____%'
In this case we look for any value that contains something in the pattern DD/MM/YYYY. The _ signals to the query that can be any value.
Then we need to remove the waste. We can do this using the function Translate.
"We translate "everything that is between what we want, ie "0123456879/ ".
translate(campo, '0123456789/',' ')
And the result, we translate again, using the result of the above function.
translate(campo,(translate(campo, '0123456789/',' ')), ' ')
We can add a TRIM to remove the remaining spaces and search for results that have only 10 characters, that is the size of our standard DD/MM/YYYY, leaving something like this:
WITH dados AS (
SELECT 'TESTE 01/01/2020' dt FROM DUAL UNION ALL
SELECT '01/01/2020 TESTE' dt FROM DUAL UNION ALL
SELECT 'TESTE 01/01TESTE/2020' dt FROM DUAL UNION ALL
SELECT 'TESTE AA/01/2020' dt FROM DUAL UNION ALL
SELECT 'TESTE AA/BB/2020' dt FROM DUAL UNION ALL
SELECT 'TESTE 0E1/01/20E20' dt FROM DUAL UNION ALL
SELECT 'TESTE 0E1/01/20' dt FROM DUAL UNION ALL
SELECT 'TESTE 0E1/JAN/20' dt FROM DUAL union all
SELECT 'TESTE 2020/01/20' dt FROM DUAL
)
,tratados as (
SELECT trim(translate(dt,(translate(dt, '0123456789/',' ')), ' ')) dt
FROM dados
WHERE dt like '%__/__/____%'
or dt like '%__/__/__%'
or dt like '%__/___/__%'
or dt like '%____/__/__%')
SELECT CASE WHEN dt like '__/__/____' THEN
to_date(dt,'dd/mm/yyyy')
WHEN dt like '__/__/__' THEN
to_date(dt,'dd/mm/yy')
WHEN dt like '__/___/__' THEN
to_date(dt,'dd/mon/yy')
WHEN dt like '____/__/__' THEN
to_date(dt,'yyyy/mm/dd')
END dt
FROM tratados
WHERE (dt like '%__/__/____%'
or dt like '%__/__/__%'
or dt like '%__/___/__%'
or dt like '%____/__/__%')
See an example running here.
And so it goes... just keep setting the standards...
Browser other questions tagged sql oracle
You are not signed in. Login or sign up in order to post.
Yes, if the back structure has a mandatory format, then it is only format for the date type. example:
to_date('10052020','dd/mm/yyyy')– David
If the date can appear in any part of the text and there is no clear pattern (for example, if there is a separator, or a text pattern that appears before or after, etc.), then I guess there is not much way.
– hkotsubo