关于Oracle和PostgreSQL中非正常日期提取并转换

今天遇到一个需求,表中有一个字段，字段存储的值是 “24年3月6日版本V1” , “24年2月22是版本V1”, “ABC-0222-V1”,“上传于23年2月22日版本V1”；现在要将这种以24年开头的日期提取出来并转换成正常的日期格式如：YYYY-MM-DD 的格式；本想着一个个截取然后在将截取后的值进行拼接，但是一想需要截取的值实在是太多了，果断放弃，选择了更好的方法采用正则表达式来实现；
下面就通过代码来实现一下Oracle(19c)和PostgreSQL中如何将这种数据提取并转换；

1.创建一个测试数据表

Oracle数据库版本：

1.1.Oracle建表

-- 删除数据表 -- 
DROP TABLE TEST ;

-- 创建数据表 --
CREATE TABLE TEST(
  COL_NAME VARCHAR(50)
);

1.2.插入数据

-- 插入数据 -- 
INSERT INTO TEST VALUES('24年3月6日版本V1');
INSERT INTO TEST VALUES('24年2月22日版本V1');
INSERT INTO TEST VALUES('ABC-0222-V1');
INSERT INTO TEST VALUES('上传于23年2月22日版本V1');

1.3.查询数据

SELECT * FROM TEST T ;

1.4.日期提取

SELECT COALESCE(TO_CHAR(TO_DATE(T1.NEW_NAME, 'YY"年"MM"月"DD"日"'),
                        'YYYY-MM-DD'),
                OLD_NAME) AS COL_NAME
  FROM (SELECT CASE
               -- 判断是否是数字开头 --
                 WHEN REGEXP_LIKE(T.COL_NAME1, '^[0-9]') THEN
                  T.COL_NAME1
               END NEW_NAME
              ,CASE
               -- 判断非数字开头 -- 
                 WHEN REGEXP_LIKE(T.COL_NAME1, '^[^0-9]') THEN
                  T.COL_NAME1
               END OLD_NAME
          FROM (SELECT SUBSTR(COL_NAME, 1, INSTR(COL_NAME, 'V') - 2) COL_NAME1
                  FROM TEST) T) T1

注意：

在Oracle11G上测试 TO_DATE(‘24年3月6日’ , ‘YY"年"MM"月"DD"日"’) 这种语法直接报错，想使用这个功能的小伙伴，建议升级一下数据库；

2.PostgreSQL建表

这里的数据跟Oracle中的数据是一样的，所以这里就不贴图了 

2.1.建表

-- 删除数据表 -- 
DROP TABLE TEST ;

-- 创建数据表 --
CREATE TABLE TEST(
  COL_NAME VARCHAR(50)
) ;

2.2.插入数据

-- 插入数据 -- 
INSERT INTO TEST VALUES('24年3月6日版本V1');
INSERT INTO TEST VALUES('24年2月22日版本V1');
INSERT INTO TEST VALUES('ABC-0222-V1');
INSERT INTO TEST VALUES('上传于23年2月22日版本V1');

2.3.查询数据

SELECT * FROM TEST T ;

2.4.日期提取

SELECT COALESCE(TO_CHAR(TO_DATE(T1.NEW_NAME, 'YY"年"MM"月"DD"日"'),
                        'YYYY-MM-DD'),
                OLD_NAME) AS COL_NAME
  FROM (SELECT CASE
               -- 判断是否是数字开头 --
                 WHEN T.COL_NAME1 ~'^[0-9]' THEN
                  T.COL_NAME1
               END NEW_NAME
              ,CASE
               -- 判断非数字开头 -- 
                 WHEN T.COL_NAME1 ~'^[^0-9]' THEN
                  T.COL_NAME1
               END OLD_NAME
          FROM (SELECT SUBSTR(COL_NAME, 1, INSTR(COL_NAME, 'V') - 2) COL_NAME1
                  FROM TEST) T) T1