oracle 如何在oracle中将varchar2转换为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7051090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to convert varchar2 to number in oracle?
提问by Tom Hymany
I have a table with many records and it continues to grow:
我有一个包含许多记录的表，并且它继续增长：
table T_A
{
total varchar2(10),
total number
}
The "total" field composed with 5 numbers and 1 character, e.g. "12345p", the character is either "p" or "N".
“总计”字段由5个数字和1个字符组成，例如“12345p”，字符为“p”或“N”。
Now, I want to write a trigger to convert existing "total" to numbers and store them in "total_num". furthermore, if there are inserting or updating operations, it can automatically finishes this conversion, and it must satisfy the following condition:
现在，我想编写一个触发器来将现有的“total”转换为数字并将它们存储在“total_num”中。此外，如果有插入或更新操作，它可以自动完成这个转换，它必须满足以下条件：
if the character is "p", the number is positive, e.g. "12345p" converts to "12345", otherwise, the number is negative. e.g. "12345N" converts to "-12345".
如果字符是“p”，则数字为正数，例如“12345p”转换为“12345”，否则为负数。例如“12345N”转换为“-12345”。
How to write this trigger?
这个触发器怎么写？
回答by Kevin Burton
Try this
尝试这个
(not tested as I don't have an oracle connection at the moment)
（未测试，因为我目前没有 oracle 连接）
CREATE OR REPLACE TRIGGER trg_ta_totals
BEFORE INSERT OR UPDATE ON T_A
FOR EACH ROW
DECLARE
vnum number;
BEGIN
IF instr(:NEW.total,'p') > 0 THEN
vnum:=TO_NUMBER(SUBSTR(:NEW.total,0,LENGTH(:NEW.total)-1));
ELSIF instr(:NEW.total,'N') > 0 THEN
vnum:=TO_NUMBER(SUBSTR(:NEW.total,0,LENGTH(:NEW.total)-1))*-1;
END IF;
:NEW.total_num := vnum;
END;
