从varchar SQL Developer中删除前导零 [英] Removing leading zeros from varchar sql developer
问题描述
如何从varchar形式的数字中删除前导零. 我尝试了以下方法:
How would I remove the leading zeros from a number which is in the form of varchar. I have tried the following:
选项1:
insert into example_table (columnName)
(SELECT
SUBSTR(columnName2, InStr('%[^0 ]%', columnName2 + ' '), 10)
from columnName2);
有了这个,我得到的错误是
With this, the error I get is
SQL Error: ORA-01722: invalid number
ORA-02063: preceding line from xxxx
01722. 00000 - "invalid number"
选项2:
insert into example_table (columnName)
(SELECT
SUBSTR(columnName2, InStr('%[^0 ]%', columnName2 + ' '),
LEN(columnName2))
from columnName2);
这次我得到
Error at Command Line:23 Column:87
Error report:
SQL Error: ORA-00904: "LEN": invalid identifier
选项3:
SUBSTRING
(columnName2, PATINDEX('%[^0 ]%', columnName2 + ' '), 10));
类似,我明白了
Error at Command Line:23 Column:41
Error report:
SQL Error: ORA-00904: "PATINDEX": invalid identifier
00904. 00000 - "%s: invalid identifier"
编辑
我认为修整路线可能是我最好的选择,但是...我不确定如何使用.
I think that the trim route might be my best option, however... I am uncertain how to use it in the case I have.
INSERT INTO temp_table
(columnNeedTrim, column2, column3, column4, column5)
SELECT * FROM
(SELECT(
SELECT TRIM(leading '0' from columnNeedTrim) FROM table),
table.column2,
table2.column3,
table.column4
table.column5
FROM
table
INNER JOIN
table2 ON
table1.columnNeedTrim=table2.columnNeedTrim)
WHERE NOT EXISTS (SELECT * FROM temp_table);
我现在得到一个错误,因为我的修整函数返回了多行结果.
I now get an error because my trim function returns multiple row result.
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
我不确定如何对上面的陈述进行修饰(或强制转换).有什么帮助吗? 感谢您的帮助!
I am not sure how to work a trim (or a cast) into the statement above. Any help on that? Thanks for any help!
推荐答案
Oracle对字符串具有内置的TRIM
函数.假设您有一个类似'00012345'
的字符串,并且想要将其保留为字符串,而不是将其转换为实际的NUMBER
,则可以使用
Oracle has built-in TRIM
functions for strings. Assuming you have a string like '00012345'
and you want to keep it as a string, not convert it to an actual NUMBER
, you can use the LTRIM
function with the optional second set
parameter specifying that you're triming zeros:
select ltrim('000012345', '0') from dual;
LTRIM
-----
12345
如果您还可能有前导空格,则可以一次性修整两者:
If you might also have leading spaces you can trim both in one go:
select ltrim(' 00012345', '0 ') from dual;
LTRIM
-----
12345
您还可以转换成数字并返回,但是除非您想删除其他格式,否则这似乎需要做很多工作:
You could also convert to a number and back, but that seems like a lot of work unless you have other formatting that you want to strip out:
select to_char(to_number('000012345')) from dual;
顺便说一句,您第一次尝试得到ORA-01722的直接原因是您使用的是数字+
运算符,而不是Oracle的字符串集中运算符||
.它正在将您的字符串隐式转换为数字,这似乎是您要避免的操作,并且单个空格的隐式转换-无论是什么用途-都会导致错误. (实际上,您的某些值可能根本不是数字,这是为什么数字应存储在NUMBER
字段中的另一个示例;如果是这种情况,那么仍然可以将(转换为)数字转换为数字并返回ORA-01722).如果使用LENGTH
而不是LEN
,则在第二次尝试中将得到相同的结果.无论如何都不会起作用,因为INSTR
无法识别正则表达式.您可以改用REGEXP_INSTR
,但是如果您想走这条路,最好使用@schurik的REGEXP_REPLACE
版本.
Incidentally, the immediate reason you get the ORA-01722 from your first attempt is that you're using the numeric +
operator instead of Oracle's string concentenation operator ||
. It's doing an implicit conversion of your string to a number, which it seems you're trying to avoid, and the implicit conversion of the single space - whatever that is for - is causing the error. (Possibly some of your values are not, in fact, numbers at all - another example of why numbers should be stored in NUMBER
fields; and if that is the case then converting (or casting) to a number and back would still get the ORA-01722). You'd get the same thing in the second attempt if you were using LENGTH
instead of LEN
. Neither would work anyway as INSTR
doesn't recognise regular expressions. You could use REGEXP_INSTR
instead, but you'd be better off with @schurik's REGEXP_REPLACE
version if you wanted to go down that route.
我不确定我是否理解您的问题编辑内容.看来您的插入内容可以简化为:
I'm not sure I understand your question edit. It looks like your insert can be simplified to:
INSERT INTO temp_table (columnNeedTrim, column2, column3, column4, column5)
SELECT LTRIM(table1.columnNeedTrim, '0 '),
table1.column2,
table1.column3,
table1.column4,
table1.column5
FROM table1
INNER JOIN table2 ON table2.columnNeedTrim = table1.columnNeedTrim
WHERE NOT EXISTS (
SELECT * FROM temp_table
WHERE columnNeedTrim = LTRIM(t42.columnNeedTrim, '0 '));
(我不明白为什么您要在您的版本中执行子查询,或者为什么要从另一个子查询中获得修整后的值.)
(I don't understand why you're doing a subquery in your version, or why you're getting the trimmed value from another subquery.)
您也可以使用MERGE
:
MERGE INTO temp_table tt
USING (
SELECT LTRIM(t42.columnNeedTrim, '0 ') AS columnNeedTrim,
t42.column2,
t42.column3,
t42.column4,
t42.column5
FROM t42
INNER JOIN t43 ON t43.columnNeedTrim=t42.columnNeedTrim
) sr
ON (sr.columnNeedTrim = tt.columnNeedTrim)
WHEN NOT MATCHED THEN
INSERT (tt.columnNeedTrim, tt.column2, tt.column3, tt.column4, tt.column5)
VALUES (sr.columnNeedTrim, sr.column2, sr.column3, sr.column4, sr.column5);
这篇关于从varchar SQL Developer中删除前导零的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!