Заявление Oracle Update: проблема с действительной проверкой даты

Поэтому я работаю над исправлением таблицы (мы будем называть этот "тест") тысячами записей, требующих очистки данных. Столбец (мы будем называть этот "textlog") таблицы, которую я исправляю, в настоящее время является типом данных varchar2. Обычно столбец начинается с даты в любом количестве форматов, за которым следует описание, подобное примерам ниже:

03/01/05-randomtext randomtext
03/01/2005 - randomtext randomtext
03/1/05//randomtext randomtext
03/1/2005 randomtext randomtext
3.01.2005 randomtext randomtext
3.01.05//randomtext randomtext
3-1-05 - randomtext randomtext
3-1-2005 randomtext randomtext
2005/03/01 - randomtext randomtext
2005/3/1//randomtext randomtext
2005.3.01 randomtext randomtext
2005-03-1 randomtext randomtext

Все эти данные были введены вручную, поэтому он в этом кошмаре формата. Однако, как показано выше, ТОЛЬКО персонажи, когда-либо разделяющие месяцы, дни и годы, это "/", "-" или ".".

Что мне нужно сделать, это изменить все данные в этом столбце, чтобы, если он начинается с даты, он изменяется на формат RRRR/MM/DD, а затем следует строка текста (все еще содержащаяся в том же столбце). Поэтому вышеприведенные данные должны были бы выглядеть так, когда я закончил с ним:

2005/03/01 randomtext randomtext

Некоторые столбцы сначала начинаются с текста, но все это можно игнорировать. Меня интересуют только записи, начинающиеся с даты. Проблема, с которой я сталкиваюсь, иногда кто-то совершил огромную ошибку, вводя данные и помещая недопустимую дату (например, январь 0), и мой запрос останавливается там. Я не могу быть уверен, сколько там недействительных дат или какие записи они есть, поэтому было бы лучше просто игнорировать их.

Ниже мой текущий запрос, который сталкивается с проблемами, когда встречается недействительная дата. Поскольку я уверен, что вы сможете узнать из моего кода, я довольно новичок в SQL, поэтому я был бы признателен за любую помощь, которую вы можете предоставить, независимо от того, работает ли она с моим текущим кодом, предлагая совершенно другой подход.

Примечание. Этот код настроен таким образом, чтобы отображать все текущие даты форматов (см. Выше в моем примере)

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 10), to_char(to_date(substr(logtext, 1, 10), 'MM/DD/RRRR'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]\.[0-9][0-9]\.[0-9][0-9][0-9][0-9][^0-9]');

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 9), to_char(to_date(substr(logtext, 1, 9), 'MM/DD/RRRR'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]-[0-9]-[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]\.[0-9]\.[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]\.[0-9][0-9]\.[0-9][0-9][0-9][0-9][^0-9]');

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 8), to_char(to_date(substr(logtext, 1, 8), 'MM/DD/RRRR'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9][0-9]/[0-9][0-9]/[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]/[0-9]/[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]-[0-9][0-9]-[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]-[0-9]-[0-9][0-9][0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]\.[0-9][0-9]\.[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]\.[0-9]\.[0-9][0-9][0-9][0-9][^0-9]');

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 7), to_char(to_date(substr(logtext, 1, 7), 'MM/DD/RRRR'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9][0-9]/[0-9]/[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]/[0-9][0-9]/[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]-[0-9]-[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]-[0-9][0-9]-[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9]\.[0-9]\.[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]\.[0-9][0-9]\.[0-9][0-9][^0-9]');

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 6), to_char(to_date(substr(logtext, 1, 6), 'MM/DD/RRRR'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9]/[0-9]/[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]-[0-9]-[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9]\.[0-9]\.[0-9][0-9][^0-9]');

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 10), to_char(to_date(substr(logtext, 1, 10), 'RRRR/MM/DD'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]\.[0-9][0-9]\.[0-9][0-9][^0-9]');

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 9), to_char(to_date(substr(logtext, 1, 9), 'RRRR/MM/DD'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]/[0-9]/[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]-[0-9]-[0-9][0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]\.[0-9][0-9]\.[0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]\.[0-9]\.[0-9][0-9][^0-9]');

update test
set logtext = REPLACE(logtext, substr(logtext, 1, 8), to_char(to_date(substr(logtext, 1, 8), 'RRRR/MM/DD'), 'RRRR/MM/DD'))
 where regexp_like(logtext, '^[0-9][0-9][0-9][0-9]/[0-9]/[0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]-[0-9]-[0-9][^0-9]')
 or regexp_like(logtext, '^[0-9][0-9][0-9][0-9]\.[0-9]\.[0-9][^0-9]');

Благодарю!

1 ответ

Create table T_53337_tmp (unparsed varchar(100)); --input



insert into T_53337_tmp values('03/01/05-randomtext randomtext');
insert into T_53337_tmp values('03/01/2005 - randomtext randomtext');
insert into T_53337_tmp values('03/1/05//randomtext randomtext');
insert into T_53337_tmp values('03/1/2005 randomtext randomtext');
insert into T_53337_tmp values('3.01.2005 randomtext randomtext');
insert into T_53337_tmp values('3.01.05//randomtext randomtext');
insert into T_53337_tmp values('3-1-05 - randomtext randomtext');
insert into T_53337_tmp values('3-1-2005 randomtext randomtext');
insert into T_53337_tmp values('2005/03/01 - randomtext randomtext');
insert into T_53337_tmp values('2005/3/1//randomtext randomtext');
insert into T_53337_tmp values('2005.3.01 randomtext randomtext');
insert into T_53337_tmp values('2005-03-1 randomtext randomtext');


Update T_53337_tmp set unparsed= REGEXP_REPLACE(unparsed,
 '^([0-9]+)([. /-]+)([0-9]+)([. /-]+)([0-9]+)([. /-]+)(.*)$'
 ,'\1-\3-\5');

на этом этапе у вас есть данные ниже

03-01-05 
03-01-2005 
03-1-05 
03-1-2005 
3-01-2005 
3-01-05 
3-1-05 
3-1-2005 
2005-03-01 
2005-3-1 
2005-3-01 
2005-03-1

Еще одна важная вещь заключается в том, что вы можете анализировать неправильные значения.Если вы получите 02-01-12, это может быть 02 января 2012 года или 01 февраля 2012 года или даже 12 января 2002 года.

После того, как вы выяснили, как решить выше логическую проблему: используйте пользовательскую функцию для проверки действительности даты.

(от https://asktom.oracle.com/pls/asktom/f?p=100:11:0::P11_QUESTION_ID:890580198758)

CREATE OR REPLACE FUNCTION MY2DATE (p_str IN VARCHAR2
 ,format_picture IN VARCHAR2
)
 RETURN DATE
IS
BEGIN
 RETURN TO_DATE(p_str, format_picture);
EXCEPTION
 WHEN OTHERS
 THEN
 RETURN NULL;
END;
/

Ваш последний оператор обновления будет (пример только для одного случая: DD-MM-YYYY от ввода ниже: вам может потребоваться написать одно обновление для каждого формата, который, по вашему мнению, действителен сверху). (SQL NOT TESTED)

Update T_53337_tmp 
set unparsed= 
to_char(MY2DATE(unparsed,'DD-MM-YYYY'),'YYYY/MM/DD') 
where MY2DATE(unparsed,'DD-MM-YYYY') is not null;

(при условии, что окончательный единый формат вам нужен: "ГГГГ/ММ/ДД")

licensed under cc by-sa 3.0 with attribution.