Remove extra zeros from string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Remove extra zeros from string

Author

Topic

malonep
Starting Member

1 Post

Posted - 2009-05-19 : 18:01:05

Hi,

I am building a string which contains a numeric(6,3) value from the database. I need to remove the extra zeros after the decimal place but I am unsure how to do this. (ideally this could be done in one line (so it can be part of a select statement call).

e.g. if my number is 43.030 then I want to display 43.03, if my number is 90.000 then I want to show 90 etc.

I am assuming this would be a string manipulation but cannot figure out how to do this.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-19 : 19:43:20

Ideally you'd do this in your application and not in SQL. This is a presentation issue and should be handled in the presentation layer which is your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

baburk
Posting Yak Master

108 Posts

Posted - 2009-05-20 : 07:29:50

Formating in sql server is not good

SELECT CAST(90.000 AS DECIMAL(10, 2))

OR

SELECT LEFT(CONVERT(VARCHAR, 90.000), LEN( CONVERT(VARCHAR, 90.000)) - CHARINDEX('.', CONVERT(VARCHAR, 90.000), 0) + 2)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 07:42:39

Another method:

SELECT CASE WHEN numberCol=Round(numberCol,0) THEN STR(numberCol,3,0)
ELSE REPLACE(RTRIM(REPLACE(STR(numberCol,6,3), '0', ' ')), ' ','0') END
FROM myTable

As you can see, it's pretty ridiculous to do this in SQL, as was said earlier, it's better to do this in your presentation layer.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-20 : 11:45:49

quote:
Originally posted by baburk

Formating in sql server is not good

SELECT CAST(90.000 AS DECIMAL(10, 2))

OR

SELECT LEFT(CONVERT(VARCHAR, 90.000), LEN( CONVERT(VARCHAR, 90.000)) - CHARINDEX('.', CONVERT(VARCHAR, 90.000), 0) + 2)

Unless I'm missing something neither of those solutions work.

flanjerka
Starting Member

1 Post

Posted - 2009-09-26 : 00:10:35

Hey guys, I had to sign up just to post a better solution. I googled "extra zeros" + decimal + SQL Server and couldn't believe there wasn't a better answer posted anywhere. Just build a scalar function you can call in your procedure. Pass the decimal value, variable or column you want to remove the extra zeros from to the function, and it returns a trimmed string:


CREATE FUNCTION [dbo].[fnTrimZeros] 
(
	@decValue decimal(7,5)
)
RETURNS varchar(9)
AS
BEGIN
DECLARE @txtTrimmed varchar(9), @chrTest varchar(1)

SELECT @txtTrimmed = Convert(varchar(8),@decValue)
SELECT @chrTest =  Substring(@txtTrimmed,Len(@txtTrimmed),1)
WHILE @chrTest = '0'
BEGIN
	SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))
	SELECT @chrTest =  substring(@txtTrimmed,len(@txtTrimmed),1)
END
IF @chrTest = '.' -- remove unnecessary decimal point
	SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))

	RETURN @txtTrimmed + '%' -- optional % sign formatting

END

Call the function by:

SELECT dbo.fnTrimZeros(@DecimalVariable) AS TrimmedDecimal

-or-

SELECT dbo.fnTrimZeros(DecimalColumn) AS TrimmedDecimal FROM dbo.DecimalTable

You may need to adjust the precision of the decimals, or length of the varchars to suit your needs. Also, if you convert the trimmed string back to a decimal, you're probably just going to add extra zeros back on since you can't predict the precision of the decimal on the fly... I agree this should be done on the presentation layer (when possible, 'cause sometimes it's not!), but string manipulation in SQL Server isn't impossible!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-28 : 07:35:44

quote:
Originally posted by flanjerka

Hey guys, I had to sign up just to post a better solution.

... I agree this should be done on the presentation layer (when possible, 'cause sometimes it's not!), but string manipulation in SQL Server isn't impossible!

The while loop function you found costs less than the simple set based statement Rob posted?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources