如何在字符串中查找特定的掩码-Oracle? [英] How to find a specific mask within a string - Oracle?

问题描述

我在表中有一个字段，可以用不同的值通知该字段. 范例:

I have a field in a table that can be informed with differente values. Examples:

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

我必须标识具有日期掩码'dd/mm/yyyy'的字符串的行.像第3行一样，因此我可以向其中添加一个TO_DATE函数.

I have to identify the rows that have a string with a date mask 'dd/mm/yyyy' informed. Like Row 3, so I may add a TO_DATE function to it.

关于如何在野外搜寻口罩的任何想法?

Any idea on how can I search a mask within the field?

非常感谢

推荐答案

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

Sounds like a data model problem (storing a date in a string).

但是，由于它发生了，而且我们有时无法控制或更改某些东西，所以我通常会像下面这样保留一个函数:

But, since it happens and we sometimes can't control or change things, I usually keep a function around like this one:

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;

这篇关于如何在字符串中查找特定的掩码-Oracle?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！