CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012sql-server-2008-r2
Tayyab Amin
Tayyab Amin

Reputation: 139

SQL Server: How to return rounded value by removing zeros

I have the values in column 01 which i want to convert as values in Column 02, removing all the zero's from end. Is there a way to do this in SQL server ? 

Column 01    Column 02 
20100000   201
29050000   2905

Thanks in anticipation.

Upvotes: 0

Views: 62

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

For positive integers only you could use:

CREATE TABLE #tab(col1 INT);
INSERT INTO #tab(col1) VALUES (20100000),(29050000);

SELECT col1, REVERSE(REVERSE(col1) + 0) + 0 AS trimmed
FROM #tab;

LiveDemo

Output:

╔══════════╦═════════╗
║   Col1   ║ trimmed ║
╠══════════╬═════════╣
║ 20100000 ║     201 ║
║ 29050000 ║    2905 ║
╚══════════╩═════════╝

How it works:

  1. Reverse number -> string
  2. Add 0 -> implicit cast to INT. You could use CAST/CONVERT to be explicit !
  3. Reverse one again -> string
  4. Add 0 -> cast to INT

20100000 -> "0000102" -> 102 -> "201" -> 201

Addendum

Added support for negative integer as well:

SELECT col1, SIGN(col1) * REVERSE(REVERSE(ABS(col1)) + 0) AS trimmed
FROM #tab;

LiveDemo2

Upvotes: 7

The_Player
The_Player

Reputation: 171

Or another way is to create a function to remove excess zeros, like this:

CREATE FUNCTION dbo.RemoveZeros(
   @value BIGINT
) RETURNS BIGINT AS
BEGIN
    DECLARE @str NVARCHAR(50)
    SET @str = CAST(@value AS NVARCHAR(50))
    WHILE ((LEN(@str)>0) AND (RIGHT(@str,1)=N'0'))
        SET @str = LEFT(@str,LEN(@str)-1)

    RETURN @str
END

Upvotes: 0

Related Questions