Oracle中CHAR列转NUMBER列并添加至表的纯SQL方案咨询

我需要将CHAR列转换为NUMBER列并添加回表中，存在以下情况：

• 小数分隔符为点或逗号，无千位分隔符，例如1,000和1.000均表示1而非1000。
• 部分值需先编辑再转换。

由于只能通过R的{DBI}和{odbc}包访问数据库，因此需要纯SQL解决方案（不使用PL/SQL）。

可复现示例 #

我并非数据库管理员，无法将表定义改为使用VARCHAR2。

CREATE TABLE mytab (
 txt CHAR(6)
);

INSERT INTO mytab VALUES ('1');
INSERT INTO mytab VALUES ('0.1'); -- 点分隔符
INSERT INTO mytab VALUES ('0,1'); -- 逗号分隔符
INSERT INTO mytab VALUES ('two'); -- 需要转换为2

根据我的会话参数，无法直接转换0.1（two也不行）：

SELECT value
FROM nls_session_parameters
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
-- 返回 ', '

SELECT VALIDATE_CONVERSION(txt AS NUMBER) AS is_num
FROM mytab;
-- 返回 1 0 1 0

方案1 #

设置合适的NLS_NUMERIC_CHARACTERS进行SELECT可成功（按@PaulW建议使用RTRIM()）：

SELECT
  CASE
    WHEN txt = '0.1'
    THEN TO_NUMBER(RTRIM(txt), '9D9', 'NLS_NUMERIC_CHARACTERS=''.,''')
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt)
  END AS num
FROM mytab;
-- 返回 1,0 0,1 0,1 2,0

但添加该列回表时会触发ORA-01722: invalid number错误：

ALTER TABLE mytab
ADD num AS (
  CASE
    WHEN txt = '0.1'
    THEN TO_NUMBER(RTRIM(txt), '9D9', 'NLS_NUMERIC_CHARACTERS=''.,''')
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt)
  END
);

SELECT num FROM mytab;
-- 报错ORA-01722

ALTER TABLE mytab DROP COLUMN num;

方案2 #

类似地，在合适场景下使用REPLACE(..., '.', ',')进行SELECT可成功：

SELECT
  CASE
    WHEN txt = '0.1'
    THEN TO_NUMBER(REPLACE(txt, '.', ','))
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt)
  END AS num
FROM mytab;
-- 返回 1,0 0,1 0,1 2,0

但添加列回表时同样触发ORA-01722错误：

ALTER TABLE mytab
ADD num AS (
  CASE
    WHEN txt = '0.1'
    THEN TO_NUMBER(REPLACE(txt, '.', ','))
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt)
  END
);

SELECT num FROM mytab;
-- 报错ORA-01722

ALTER TABLE mytab DROP COLUMN num;

方案3 #

尝试分两步使用临时列，但触发ORA-54012: virtual column is referenced in a column expression错误：

ALTER TABLE mytab
ADD temp AS (
  REPLACE(txt, '.', ',')
);

ALTER TABLE mytab
ADD num AS (
  CASE
    WHEN temp = 'two'
    THEN 2
    ELSE TO_NUMBER(temp)
  END
);
-- 报错ORA-54012

方案4 #

先执行UPDATE替换分隔符再添加列，同样触发ORA-01722错误：

UPDATE mytab
SET txt = REPLACE(txt, '.', ',');

ALTER TABLE mytab
ADD num AS (
  CASE
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt)
  END
);

SELECT num FROM mytab;
-- 报错ORA-01722

ALTER TABLE mytab DROP COLUMN num;

问题 #

• 在不修改表定义的前提下，将CHAR列转为NUMBER列并添加至表的最佳纯SQL方案是什么？
• 为何方案1和2在SELECT时可行，但添加列回表时失败？
• 为何方案1、2、4会失败？以下测试结果更让我困惑：

-- 方案1变体
ALTER TABLE mytab
ADD num1 AS (
  CASE
    WHEN txt = '0.1'
    THEN TO_NUMBER(RTRIM(txt) DEFAULT NULL ON CONVERSION ERROR, '9D9', 'NLS_NUMERIC_CHARACTERS=''.,''')
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt DEFAULT NULL ON CONVERSION ERROR)
  END
);

SELECT num1 FROM mytab;
-- 返回 1 0,1 NULL 2

-- 方案2变体
ALTER TABLE mytab
ADD num2 AS (
  CASE
    WHEN txt = '0.1'
    THEN TO_NUMBER(REPLACE(txt, '.', ',') DEFAULT NULL ON CONVERSION ERROR)
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt DEFAULT NULL ON CONVERSION ERROR)
  END
);

SELECT num2 FROM mytab;
-- 返回 1 NULL NULL 2

-- 方案4变体
UPDATE mytab
SET txt = REPLACE(txt, '.', ',');

ALTER TABLE mytab
ADD num4 AS (
  CASE
    WHEN txt = 'two'
    THEN 2
    ELSE TO_NUMBER(txt)
  END
);

SELECT num4 FROM mytab;
-- 返回 1 NULL NULL 2

内容的提问来源于stack exchange，提问作者Thomas