Removing zeros from number

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

Solution 1:

Here is the solution (MySQL) for Hackerrank challenge The Blunder

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.

Solution 2:

  REPLACE('0349010330', '0', '');

this also work

  REPLACE('0349010330', '0');

Solution 3:

Oracle/PLSQL

SELECT TO_NUMBER(REPLACE(TO_CHAR(0349010330),'0','')) FROM DUAL;

MySQL

SELECT CONVERT(REPLACE(STR(0349010330),'0',''),INTEGER) FROM DUAL;

Solution 4:

No need to convert it into string to use replace

MySQL solution

SELECT CEIL(AVG(SALARY)-AVG(REPLACE(SALARY,"0",""))) FROM EMPLOYEES ; 

Solution 5:

HackerRank Problem link

Solution Here ->

`SELECT ROUND(AVG(SALARY)-AVG(TO_NUMBER(REPLACE(TO_CHAR(SALARY),'0')))+.5,0) FROM EMPLOYEES;`