Suppress Trailing Zeros?
-
Is it possible to suppress trailing zeros using t-sql?
I have a numeric column, Decimal(18,10). I would like to concatenate the data with some character data. When I use cast or convert it keeps all of the trailing zeros.
Data looks like this:
0.2000000000
3.0000000000
100.0000000000
0.0016500000
I want this:
0.2
3
100
0.00165
(I would settle for this:)
0.2
3.0
100.0
0.00165
I can't rely on formatting it on the client side because of the concatenation with character data.
Thanks in advance,
Greg.
-
You could convert to char and then look for the first 0 after the decimal and use that as a limiting factor for your substring.
Steve Jones
-
Much of the data is quite small in value, so that wouldn't work for me. the .0016500000 would become 0.0. I need to keep all of the non-zero digits in my output. I suppose I COULD use a WHILE to start from the right and chop off the zeros 1 by 1...
-
That's probably what I would do. After looking at this more (and it's interesting), I think you need to chop from the right. Seems to work as long as you don't have to convert back to numeric.
Steve Jones
-
OK, here's what I did. I had trouble using WHILE inside the select query...used CASE instead) I know that there are up to 10 0s past the decimal with my data.
SET NOCOUNT ON
--create a test table
CREATE TABLE #tmp (ADJ_VALUE DECIMAL(18,10))
--insert data
INSERT INTO #tmp (ADJ_VALUE) VALUES (100.0000000000)
INSERT INTO #tmp (ADJ_VALUE) VALUES (3.0000000000)
INSERT INTO #tmp (ADJ_VALUE) VALUES (0.2000000000)
INSERT INTO #tmp (ADJ_VALUE) VALUES (0.0016500000)
INSERT INTO #tmp (ADJ_VALUE) VALUES (0.0000000009)
--Run the test
SELECT
CASE
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 11) = '.0000000000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 11 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 10) = '0000000000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 10 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 9 ) = '000000000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 9 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 8 ) = '00000000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 8 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 7 ) = '0000000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 7 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 6 ) = '000000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 6 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 5 ) = '00000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 5 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 4 ) = '0000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 4 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 3 ) = '000'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 3 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 2 ) = '00'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 2 )
WHEN RIGHT(CONVERT(Varchar(20),ADJ_VALUE), 1 ) = '0'
THEN LEFT(CONVERT(Varchar(20),ADJ_VALUE), LEN(CONVERT(Varchar(20),ADJ_VALUE)) - 1 )
ELSE CONVERT(Varchar(20),ADJ_VALUE)
END AS ADJ_VAL
FROM #tmp
--Cleanup
DROP TABLE #tmp
Not so pretty, but works!
Edited by - GregLyon on 12/31/2002 1:48:24 PM
-
An off-topic side note. I tried using the code /code identifiers when pasting my code. Everything looks nicer in the web page but it doesn't cut and paste correctly back into Query analyzer; the line breaks are all lost. Is there a preferred way to show code on this site? Am I doing something wrong?
-
Looks like in the time I used to play with it, you've come to the same conclusion I have. With a scale of ten, you get ten digits. In order to remove them, you must do string comparisons. I ended up with basically the same case statement your using.
-
Thanks Scorpion 66.
It's good to get validation that I'm barking up the right tree here. I sure did try to get it to work with WHILE, but it doesen't appear to work inside of SELECT queries, and I wonder if I'd end up truncating the underlying data even if it did.
The string comparison was important. At first I didn't put the 000s in quotes. Didn't work. It gave Integer overflow errors. apparently it was trying to convert my converted-to-string numbers back to numbers again to compare that way.
-
It is ugly and you might have to fix a bit since I couldn't test but this should do it with the need for the case, but the case still might be better.
SELECT LEFT(CAST(ADJ_VALUE AS VARCHAR(18)), CHARINDEX('.',CAST(ADJ_VALUE AS VARCHAR(18))) + CAST(CAST(SUBSTRING(CAST(ADJ_VALUE AS VARCHAR(18)), CHARINDEX('.',CAST(ADJ_VALUE AS VARCHAR(18)), 18 - CHARINDEX('.',CAST(ADJ_VALUE AS VARCHAR(18))) AS INT) AS VARCHAR(10))
-
Here is a method also ugle, but I commented the code:
declare @n decimal(18,10)
declare @C char(100)
set @n = 0.011690000000
select
-- get the part before the decimal point
substring(cast(@n as varchar(18)),1,charindex('.',cast(@n as varchar(18)))) +
-- read comments from bottom up
-- reverse back to original order
reverse(
-- cast as varchar
cast(
-- Cast as int to get rid of leading zeros, basically trailing zeros in original number
cast(
-- reverse the characters after the decimal point
reverse(
-- get the part after the decimal point
substring(cast(@n as varchar(18)),charindex('.',cast(@n as varchar(18)))+1,18)
) as int)
as varchar(18)))
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
How do those compare to the Case statement? Drat it, I'm not at my test machine to check myself. I won't be able to find out till thursday at best. I have a situation this could help me in as well, was what interested me enough to play with it, though I need to keep 2 zeroes instead of getting rid of all of them. I've been using a case statement, but if these others cause less overhead, I'll modify and use them rather than what I'm doing.
It's kinda funny, but when it was just my issue, it wasn't worth looking any further. It wasn't until I saw his issue that I decided to try and find something different. LMAO.... I guess it's a lot like machanics and their cars.
Oh, and its always a breath of fresh air to see comments in code. Just because.
Edited by - scorpion_66 on 12/31/2002 4:02:40 PM
-
Thanks Greg,
After looking at Antares' example I had begun to play with REVERSE. That's it. I made one tweak to yours though. Since there are 10 digits, I had to use DECIMAL(10,0) instead of INT. Otherwise really small numbers, when reversed, cause an integer overflow.
I tried both methods against a small (20000 rows) AND a larger (600,000 rows) dataset. They are almost exactly the same performance wise. On my test server returning 600,000 rows to the ISQLW window took 55 sec without processing the numbers and about 58 sec with processing using either the CASE or the REVERSE method. So, even though the code is ugly it doesn't really impact performance too much at all!
Happy new year all, and thanks for the help!
-
One other thing,
I added an extra character to the Varchars for the decimal (.). I changed all Varchar(18) to Varchar(19).
-
Glad to see my code worked for you.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Ahh to have a server available. Anyway here is another option that may have some bennifits as well.
select
REPLACE(RTRIM(REPLACE(LEFT(cast(decv as varchar(20)),
LEN(cast(decv as varchar(20))) -
(PATINDEX('%[.,1-9]%', REVERSE(cast(decv as varchar(20)))) - 1)), '.', ' ')), ' ' , '.') AS decv from tbldec
Replace decv with your column name and tbldec with your table name. The replace, rtrim and replace are to remove the . when value is whold like 100 and 3 in you example. If you want .0 instead then try this
select
LEFT(cast(decv as varchar(20)),
LEN(cast(decv as varchar(20))) -
(PATINDEX('%[.,1-9]%', REVERSE(cast(decv as varchar(20)))) -
(CASE WHEN PATINDEX('%[.,1-9]%', REVERSE(cast(decv as varchar(20)))) = PATINDEX('%[.]%', REVERSE(cast(decv as varchar(20)))) THEN 2 ELSE 1 END)
)) AS decv from tbldec
You could do
select
LEFT(cast(decv as varchar(20)),
LEN(cast(decv as varchar(20))) -
(PATINDEX('%[.,1-9]%', REVERSE(cast(decv as varchar(20)))) -
(CASE WHEN PATINDEX('%[.,1-9]%', REVERSE(cast(decv as varchar(20)))) = PATINDEX('%[.]%', REVERSE(cast(decv as varchar(20)))) THEN 0 ELSE 1 END)
)) AS decv from tbldec
instead of the first one but based on the number and types of operations option 1 would perform better against CPU.
Edited by - antares686 on 01/02/2003 05:59:41 AM
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply