如何仅格式化那些未抛出ORA-01843的记录? [英] How to format only those records for which ORA-01843 is not thrown?

问题描述

我有一列包含以下示例数据.列的数据类型为VARCHAR2.

I have a column with the below sample data. The data type of the column is VARCHAR2.

050211
042911
110428
110428
AO0102
JAN 31
FEB 01

当我尝试使用以下代码将列记录格式化为可读格式时，我得到了

When I try to format the column records in a readable format with below code, I am getting

ORA-01843 : Not a valid month 

SELECT to_char(to_date(TRIM(MyColumn), 'YYMMDD'), 'MM/DD/YYYY') FROM MyTable;

我相信由于记录AO0102，JAN 31、2月1日而引发了ORA-01843.

I believe ORA-01843 is thrown because of the records AO0102, JAN 31, FEB 01.

我如何忽略那些抛出ORA-01843的记录(可能只是显示其原始值)并仅格式化那些可以用普通SQL select语句格式化的记录?

How can I ignore those records(and may be just display its original value) which throw ORA-01843 and format only those records which could be formatted in plain SQL select statement?

推荐答案

使用CASE表达式检查列的状态，并且仅在有条件的情况下尝试将其解析为有效日期:

Use a CASE expression which checks the state of the column, and only conditionally tries to parse as a valid date:

SELECT
    MyColumn,
    CASE WHEN REGEXP_LIKE(MyColumn, '^\s*\d\d\d\d\d\d\s*$')
         THEN TO_CHAR(TO_DATE(TRIM(MyColumn), 'YYMMDD'), 'MM/DD/YYYY')
         ELSE MyColumn END AS new_col
FROM MyTable

但是作为一般性评论，您应该避免将日期信息以文本形式存储在表中.您现在看到了避免这种情况的原因之一.

But as a general comment, you should avoid storing date information in your tables as text. You are now seeing one of the reasons for avoiding this.

这篇关于如何仅格式化那些未抛出ORA-01843的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！