CODEWITHSUNDEEP
sqloracle-database
ArLi91
ArLi91

Reputation: 361

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

Upvotes: 33

Views: 122714

Answers (18)

Humbell
Humbell

Reputation: 37

In MS SQL, as @hrshd has mentioned, his answer was:

SELECT CEILING(AVG(SALARY))- CEILING(AVG(CAST(REPLACE(SALARY,'0','')AS 
INT))) +1 FROM EMPLOYEES;

The above problem of adding +1 would not occur when both values are of the same datatype. So, a possible solution could be:

select ceiling( avg(cast(Salary as real)) - 
avg(cast(replace(Salary,0,'') as real)) )
from EMPLOYEES;

While answering the original question, in MS SQL it is:

  1. Perform string replace operation with Replace() function.
  2. Cast it to number i.e.int, real, etc. as required.

cast( replace(a_number,0,'') as real )

Upvotes: 0

Omkar Singh
Omkar Singh

Reputation: 812

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.

Upvotes: 61

chinmay
chinmay

Reputation: 9

HackerRank Solution https://www.hackerrank.com/challenges/the-blunder/problem

in SQL Server

SELECT CAST(CEILING (AVG(CAST(SALARY AS float)) - AVG(CAST(REPLACE(salary, '0', '')AS float) ) ) as int) FROM EMPLOYEES;

Here, the actual average salary comes out to be 4046.75 and miscalculated avg salary comes out as 1794.5 Hence the difference 2252.25, which when rounded up is 2253.0, but the hackerrank solution expects it as int, so we need to again cast it back to int.

Upvotes: -1

Dakshitha96
Dakshitha96

Reputation: 57

This is the answer for removing 0 in any number.

replace('0349010330', '0', '')

Upvotes: 0

Shawly Rohman
Shawly Rohman

Reputation: 1

select 
    avg(salary) - avg(convert(integer, replace(salary,'0', ''))) + 1 
from employees

This is the answer

Upvotes: 0

ABHI SHEK
ABHI SHEK

Reputation: 85

Here is the answer using MySQL for Hackerrank problem:

SELECT round(avg(salary),0)-round(avg(replace(salary,'0','')),0) from EMPLOYEES;

you can use:

replace(Column_Name,'0','')

the above given query to remove 0 from the given integer type input

Upvotes: 0

Shashank Prasad
Shashank Prasad

Reputation: 494

No need to convert it into string to use replace

MySQL solution

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

Upvotes: 10

hrshd
hrshd

Reputation: 1001

HakerRank solution for SQL Server

SELECT CEILING(AVG(SALARY))- CEILING(AVG(CAST(REPLACE(SALARY,'0','')AS INT))) +1 FROM EMPLOYEES;

I do not know why the '+1' in the end is needed to get the correct answer.

Upvotes: 5

Deepak
Deepak

Reputation: 470

Here is answer

replace(salary,0)

A question similar to this found in hackerrank..blunder problem

select ceil(avg(salary)- avg(replace(salary,0))) from employees;

Upvotes: 1

django-unchained
django-unchained

Reputation: 844

You can use REPLACE in Oracle but remember to convert the result to number because REPLACE returns CHAR result.

select to_number(replace(1001,0,'')) from dual

Upvotes: 0

AbdulRahman Atef
AbdulRahman Atef

Reputation: 43

I think you do not need to_number( replace( to_char(x), '0', '' ) ) Instead you can simply type

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.

Upvotes: 0

kush
kush

Reputation: 173

https://www.hackerrank.com/challenges/the-blunder/problem

For Mysql, Please run below query

select ceil(Avg(Salary) - Avg(CAST(Replace(CAST(Salary as char(1000)),'0','') as unsigned))) from EMPLOYEES

Upvotes: 1

VAIBHAV GOUR
VAIBHAV GOUR

Reputation: 177

MYSQL:

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

This will replace all the Os in the string with an empty string.

Upvotes: 2

chaitanya333
chaitanya333

Reputation: 21

HackerRank Mysql Solution https://www.hackerrank.com/challenges/the-blunder/problem

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

Upvotes: 1

shankar
shankar

Reputation: 441

Oracle/PLSQL

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

MySQL

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

Upvotes: 7

Sanjit Prasad
Sanjit Prasad

Reputation: 428

HackerRank Problem link

Solution Here ->

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

Upvotes: 5

esoyler
esoyler

Reputation: 11

You can use: TO_NUMBER(REPLACE('0349010330',0))

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

this also work

  REPLACE('0349010330', '0');

Upvotes: 29

Related Questions