如何在Oracle中找到一个特定的掩码

表中有一个字段，可以用不同的值通知。例子:

Row 1 - (2012,2013) 
Row 2 - 8871 
Row 3 - 01/04/2012 
Row 4 - 'NULL'

我必须识别具有日期掩码'dd/mm/yyyy'的字符串的行。像第3行一样，所以我可以给它添加一个TO_DATE函数。

关于如何在字段内搜索掩码的任何想法?

Thanks to lot

听起来像是数据模型问题(将日期存储在字符串中)。

但是，因为它发生了，我们有时无法控制或改变事情，我通常保留一个函数，像这样:

CREATE OR REPLACE FUNCTION safe_to_date (p_string        IN VARCHAR2,
                                         p_format_mask   IN VARCHAR2,
                                         p_error_date    IN DATE DEFAULT NULL)
  RETURN DATE
  DETERMINISTIC IS
  x_date   DATE;
BEGIN
  BEGIN
    x_date   := TO_DATE (p_string, p_format_mask);
    RETURN x_date;                                                        -- Only gets here if conversion was successful
  EXCEPTION
    WHEN OTHERS THEN
      RETURN p_error_date;
  END;
END safe_to_date;

然后像这样使用:

WITH d AS
       (SELECT 'X' string_field FROM DUAL
        UNION ALL
        SELECT '11/15/2012' FROM DUAL
        UNION ALL
        SELECT '155' FROM DUAL)
SELECT safe_to_date (d.string_field, 'MM/DD/YYYY')
FROM   d;

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Test ( id, VALUE ) AS
          SELECT 'Row 1', '(2012,2013)' FROM DUAL
UNION ALL SELECT 'Row 2', '8871' FROM DUAL
UNION ALL SELECT 'Row 3', '01/04/2012' FROM DUAL
UNION ALL SELECT 'Row 4', NULL FROM DUAL
UNION ALL SELECT 'Row 5', '99,99,2015' FROM DUAL
UNION ALL SELECT 'Row 6', '32/12/2015' FROM DUAL
UNION ALL SELECT 'Row 7', '29/02/2015' FROM DUAL
UNION ALL SELECT 'Row 8', '29/02/2016' FROM DUAL
/

查询1 -您可以使用正则表达式进行检查:

SELECT *
FROM   TEST
WHERE  REGEXP_LIKE( VALUE, '^d{2}/d{2}/d{4}$' )

结果

|    ID |      VALUE |
|-------|------------|
| Row 3 | 01/04/2012 |
| Row 6 | 32/12/2015 |
| Row 7 | 29/02/2015 |
| Row 8 | 29/02/2016 |

查询2 -您可以使正则表达式更复杂，以捕获更多无效日期:

SELECT *
FROM   TEST
WHERE  REGEXP_LIKE( VALUE, '^(0[1-9]|[12]d|3[01])/(0[1-9]|1[0-2])/d{4}$' )

结果

|    ID |      VALUE |
|-------|------------|
| Row 3 | 01/04/2012 |
| Row 7 | 29/02/2015 |
| Row 8 | 29/02/2016 |

查询3 -但最好的方法是尝试将值转换为日期，看看是否有异常:

CREATE OR REPLACE FUNCTION is_Valid_Date(
  datestr VARCHAR2,
  format  VARCHAR2 DEFAULT 'DD/MM/YYYY'
) RETURN NUMBER DETERMINISTIC
AS
  x DATE;
BEGIN
  IF datestr IS NULL THEN
    RETURN 0;
  END IF;
  x := TO_DATE( datestr, format );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

SELECT *
FROM   TEST
WHERE  is_Valid_Date( VALUE ) = 1

结果

|    ID |      VALUE |
|-------|------------|
| Row 3 | 01/04/2012 |
| Row 8 | 29/02/2016 |

您可以使用like操作符来匹配模式。

where possible_date_field like '__/__/____'

相关内容

最新更新

热门标签：