CODEWITHSUNDEEP
sqlsql-server
Ranjith V
Ranjith V

Reputation: 298

Removing a digit from a number, Sql server

Table Employee:

Id  Name  Salary
--  ----  ------
1   A1    120
2   B1    105
3   C1    200

Now I want to find the Sum of Salary, by removing the 0s in salary of each employee.

i.e ; 12 + 15 + 2 = 29

Any idea on how to achieve my result ?

Thanks

Upvotes: 3

Views: 22244

Answers (5)

Sneha Bhattacharya
Sneha Bhattacharya

Reputation: 21

This code should work too:

sum(replace(salary,"0",""))

Upvotes: 0

praveen Gss
praveen Gss

Reputation: 11

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

or you can simply use replace function

Upvotes: 1

Jay Anand
Jay Anand

Reputation: 1

SELECT ROUND (AVG(Salary) - 
        AVG(CONVERT(INTEGER, REPLACE(CONVERT(VARCHAR, Salary), 0, ''))), 0 )
FROM EMPLOYEES

Upvotes: -1

Felix Pamittan
Felix Pamittan

Reputation: 31879

If Salary is of numeric type, you need to CAST it first to VARCHAR. Then use REPLACE to get rid of the 0s and then CAST it back to a numeric type before finally doing a SUM:

SELECT
    SUM(CAST(REPLACE(CAST(Salary AS VARCHAR(10)), '0', '') AS INT))
FROM Employee

Upvotes: 14

Devart
Devart

Reputation: 122002

SELECT SUM(CAST(REPLACE(t.Salary, '0', '') AS INT))
FROM (
    VALUES (120),(105),(200)
) t(Salary)

Upvotes: 1

Related Questions