今天遇到一个需求,表中有一个字段,字段存储的值是 “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;