Let's say we have value 2006 and now I want to remove 0 from it and the result is 26.
Value : 2006
After removing 0, the value is : 26
I have tried to replace directly using replace but in some case like using aggregate functions we can not use it directly.
Example where the replace is used with a grouping function
CREATE TABLE Example
(
ID Int,
Result Varchar(4)
);
INSERT INTO Example (ID,Result) VALUES
(1,'2006'),
(1,'2007'),
(1,'2008'),
(2,'2009'),
(2,'2010');
SELECT ID,
MAX(Result) as maxresult,
MAX(REPLACE(Result,'0','')) as MaxResultWithoutZeros
FROM Example
GROUP BY ID
| ID | maxresult | MaxResultWithoutZeros |
|---|---|---|
| 1 | 2008 | 28 |
| 2 | 2010 | 29 |
For Microsoft SQL Server
SELECT CONVERT(integer, REPLACE(2006,'0', ''));