February 26, 2008 at 11:19 am
we have a money feild which we need to remove the trailing zeros
for example if we have something like
inputoutput
98.767098.767
98.000098
98.010098.01
98.760098.76
can somebody please help me in figuring this out
February 26, 2008 at 11:28 am
iam using something like this but would like to know if there is a better way of doing it
DECLARE @ele money
SET @ele = 98.7670
select REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(50),convert(decimal(25,4),@ele))), PATINDEX('%[1-9]%', REVERSE(CONVERT(VARCHAR(50),convert(decimal(25,4),@ele)))),50))
February 26, 2008 at 11:47 am
Why not just apply the formatting on the UI side?
What's being displayed is what the DB is storing (money is essentially the same as a decimal value with a constant 4 places after decimal point). What you're doing would be child's play in anything actually in charge of displaying the data.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 2:44 pm
Susane,
I have created a small test case for you. Hopefully it comes back with the results you are looking for. Please post back if this is what you are looking for, if it is not, please provide us with more detail.
-- Create a temporary table for testing.
Create table #helpmoney
(
input money
)
-- Insert the sample data.
-- insert into #helpmoney values() -- blank sample row
insert into #helpmoney values(98.7670)
insert into #helpmoney values(98.0000)
insert into #helpmoney values(98.0100)
insert into #helpmoney values(98.7600)
-- Create a temporary table for testing.
Create table #helpmoney
(
input money
)
-- Insert the sample data.
-- insert into #helpmoney values() -- blank sample row
insert into #helpmoney values(98.7670)
insert into #helpmoney values(98.0000)
insert into #helpmoney values(98.0100)
insert into #helpmoney values(98.7600)
-- Display just the input (unformatted) data for the money datatype.
select input from #helpmoney
-- The default output for the money data type is 4 digits after
-- (to the right) of the decimal.
-- Use the CONVERT function to "translate" to 2 digits.
-- Display the unformatted data in the "input" column and 2 digit data
-- as the column "output".
select
input
,CONVERT(numeric(8,2),input, 2) as [output]
from #helpmoney
-- Drop the temporary table.
drop table #helpmoney
Hope This Helps,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 26, 2008 at 2:52 pm
Susane,
In response to Matt Miller's remarks, I agree. If this is a back end system for a custom application, by all means work at the application level when displaying data whenever possible. Let the database do what is does best, store the information. Code in the application for the display where decisions can be made about truncating the values or rounding for the example you have provided to us for this money column.
Regards,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply