Remove trailing zeroes from varchar in sql

Question

Remove trailing zeroes from varchar in sql

Gowsalya 21

Hi,
I need to remove trailing zeroes from varchar value, but like decimal round off in SQL server.

Examples:
M12.00 -->M12
M12.70 -->M12.7
M120 -->M120
ABC -->ABC

Is there any best way to do so?

Thanks in advance.

Gowsalya 21 Reputation points

2022-07-27T05:59:17.457+00:00

In other ways, after dot, if the last character is 0 means need to remove it

Accepted answer

1 additional answer

Your answer

Gowsalya 21 Reputation points

2022-07-27T05:59:17.457+00:00

In other ways, after dot, if the last character is 0 means need to remove it

Answer 1

LiHong-MSFT 10,056

Hi @Gowsalya
Check this query:

CREATE TABLE #TEST(Column1 VARCHAR(20))  
INSERT INTO #TEST VALUES('M12.07'),('M12.010'),('M12.00'),('M12.70'),('M120'),('ABC')  
  
SELECT CASE WHEN CHARINDEX('.',Column1)>0 AND RIGHT(Column1,1)='0'  
            THEN REVERSE(STUFF(REVERSE(Column1),1,PATINDEX('%[^0.]%',REVERSE(Column1))-1,''))  
			ELSE Column1 END  
FROM #TEST

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Gowsalya 21 Reputation points

2022-07-27T12:04:00.813+00:00

Thnks. it works fine

Answer 2

Hi @Gowsalya ,

A cleaner and more concise solution based on the TRIM() function.
It will work starting from SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (col VARCHAR(20));  
INSERT INTO @tbl VALUES  
('M12.07'),('M12.010'),('M12.00'),('M12.70'),('M120'),('ABC');  
-- DDL and sample data population, end  
  
SELECT col AS [Before]  
	, IIF(CHARINDEX('.',col) > 0 AND RIGHT(col,1) = '0',  
		TRIM('.' FROM TRIM('0' FROM col)),  
		col) AS [After]  
FROM @tbl;

Share via

Remove trailing zeroes from varchar in sql

1 additional answer

Your answer