SQL 从数字中删除零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35613341/
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
Removing zeros from number
提问by ArLi91
How can I remove zeros(not just leading zeros, but zeros in every position) from a column containing Integers(numbers)? For example : 0349010330 should become 349133
如何从包含整数（数字）的列中删除零（不仅仅是前导零，而是每个位置的零）？例如：0349010330 应该变成 349133
Thanks in advance
提前致谢
回答by Juan Carlos Oropeza
REPLACE('0349010330', '0', '');
this also work
这也有效
REPLACE('0349010330', '0');
回答by Omkar Singh
Here is the solution (MySQL) for Hackerrank challenge The Blunder
这是 Hackerrank 挑战TheBlunder 的解决方案 (MySQL)
SELECT CEIL((AVG(salary)) - (AVG(REPLACE(salary, '0', '')))) AS avg_salary FROM employees;
- REPLACE() : used to remove 0 from salary.
- AVG() : used to calculate average salary.
- CEIL() : used to get next rounded integer.
- REPLACE() ：用于从工资中删除 0。
- AVG() ：用于计算平均工资。
- CEIL() ：用于获取下一个四舍五入的整数。
回答by shankar
Oracle/PLSQL
甲骨文/PLSQL
SELECT TO_NUMBER(REPLACE(TO_CHAR(0349010330),'0','')) FROM DUAL;
MySQL
MySQL
SELECT CONVERT(REPLACE(STR(0349010330),'0',''),INTEGER) FROM DUAL;
回答by Sanjit Prasad
Solution Here ->
解决方案在这里->
`SELECT ROUND(AVG(SALARY)-AVG(TO_NUMBER(REPLACE(TO_CHAR(SALARY),'0')))+.5,0) FROM EMPLOYEES;`
回答by VAIBHAV GOUR
MYSQL:
MYSQL：
REPLACE('0349010330', '0', '')
替换('0349010330', '0', '')
This will replace all the Os in the string with an empty string.
这将用空字符串替换字符串中的所有 O。
回答by kush
https://www.hackerrank.com/challenges/the-blunder/problem
https://www.hackerrank.com/challenges/the-blunder/problem
For Mysql, Please run below query
对于 Mysql，请运行以下查询
select ceil(Avg(Salary) - Avg(CAST(Replace(CAST(Salary as char(1000)),'0','') as unsigned))) from EMPLOYEES
回答by chaitanya333
HackerRank Mysql Solution https://www.hackerrank.com/challenges/the-blunder/problem
HackerRank Mysql 解决方案 https://www.hackerrank.com/challenges/the-blunder/problem
SELECT CEIL((AVG(SALARY))-(AVG(REPLACE(SALARY,'0','')))) FROM EMPLOYEES
回答by esoyler
You can use: TO_NUMBER(REPLACE('0349010330',0))
您可以使用：TO_NUMBER(REPLACE('0349010330',0))
回答by Sa3d Clay
I think you do not need to_number( replace( to_char(x), '0', '' ) )Instead you can simply type
我认为你不需要to_number( replace( to_char(x), '0', '' ) )相反，你可以简单地输入
replace(x, '0')
replace(x, '0')
and the casting is applyed automaticaly.
Also, you do not have to specify 0 to be empty replace(x, '0', '')cause replace()by default removes the first value if you do not specify its alternative.
并且铸件是自动应用的。此外，您不必指定 0 为空，replace(x, '0', '')因为replace()如果您未指定其替代值，则默认情况下会删除第一个值。
回答by django-unchained
You can use REPLACE in Oracle but remember to convert the result to number because REPLACE returns CHAR result.
您可以在 Oracle 中使用 REPLACE 但记住将结果转换为数字，因为 REPLACE 返回 CHAR 结果。
select to_number(replace(1001,0,'')) from dual
