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 2008 Forums
 Transact-SQL (2008)
 Keep only the meaningful part of decimal value

Author  Topic 

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-21 : 15:12:16
Hello all,
is there a way to keep only the meaningful part of a decimal value?
I mean, decimal numbers until the first zero.

For example, if I have;

12.4560 -> 12.456
12.566900 -> 12.5669
12.0 -> 12
12.3456 -> 12.3456

and so on.

I'm using T-SQL for SQL Server 2008R2.

Thanks in advance.

Luigi

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-21 : 15:26:59
select
cast(123.4567 as DECIMAL(9,6))
, cast(cast(123.4567 as DECIMAL(9,6)) as float)

google schmoogle

http://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-21 : 15:36:59
Thank you Yosiasz. Maybe a simple CAST to float could works.

Luigi
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-03-21 : 17:05:51
ba bene

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-22 : 04:06:53
Just a little adding. How can I check, at first, if my variable is a real decimal and not, for example, a string?

Luigi
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-22 : 04:38:46
For example, I have these values:

1.3455000
AA34.999
11.2
11.2BBB
45.9000

and I have to convert only the numeric values, so I get:

1.3455
AA34.999
11.2
11.2BBB
45.9
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-22 : 05:25:22
DECLARE @tabDec TABLE (col1 VARCHAR(25))
INSERT INTO @tabDec
SELECT '1.3455000' union all
SELECT 'AA34.999' union all
SELECT '11.2' union all
SELECT '11.2BBB' union all
SELECT '45.9000'
/*and I have to convert only the numeric values, so I get:
1.3455
AA34.999
11.2
11.2BBB
45.9*/
select CASE WHEN ISNUMERIC(col1) = 1 THEN CAST( cast(col1 as float) AS VARCHAR(25)) ELSE col1 END
FROM @tabDec


--
Chandu
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-22 : 06:58:26
IsNumeric, right, thank you Bandi.

Luigi
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-22 : 09:39:11
quote:
Originally posted by Ciupaz

IsNumeric, right, thank you Bandi.
Luigi

Welcome Ciupaz

--
Chandu
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-22 : 11:12:20
A problem. When the number is very small, like 0.000006 it appears in esponential mode, like 1e-006.
How can I leave it 0.000006?

Luigi
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-22 : 19:20:48
Since it's already varchar, don't cast to numeric and back, just do this:


SELECT
CASE WHEN col1 LIKE '%[^0-9.-]%' OR col1 NOT LIKE '%.%' THEN col1
ELSE LEFT(col1, LEN(col1) - PATINDEX('%[^0]%', REVERSE(col1)) + 1) END
FROM @tabDec

Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-23 : 06:14:37
It works perfectly. Thank you very much Scott.

Luigi
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-23 : 09:44:34
[code]DECLARE @TabDec TABLE
(
Col1 VARCHAR(25)
)
INSERT @TabDec
SELECT '1.3455000' UNION ALL
SELECT 'AA34.999' UNION ALL
SELECT '11.2' UNION ALL
SELECT '11.2BBB' UNION ALL
SELECT '45.9000' UNION ALL
SELECT '900' UNION ALL
SELECT 'A.10' UNION ALL
SELECT 'Z.0' UNION ALL
SELECT '0.000006'

SELECT Col1 AS Original,
CASE
WHEN ISNUMERIC(Col1) = 1 THEN CAST(CAST(Col1 AS FLOAT) AS VARCHAR(25))
ELSE Col1
END AS Chandu,
CASE
WHEN Col1 LIKE '%[^0-9.-]%' OR Col1 NOT LIKE '%.%' THEN Col1
ELSE LEFT(Col1, LEN(Col1) - PATINDEX('%[^0]%', REVERSE(Col1)) + 1)
END AS Scott,
CASE
WHEN Col1 LIKE '%.%0' THEN SUBSTRING(Col1, 1, LEN(Col1) - PATINDEX('%[^.0]%', REVERSE(Col1)) + 1)
ELSE Col1
END AS SwePeso
FROM @TabDec[/code]


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-23 : 10:39:55
Great summary Swe!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-23 : 12:36:57
Not only a summary. If you look closely they return different results.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-03-23 : 15:13:08
Yes, you're right. Here the results:

Original Chandu Scott SwePeso
1.3455000 1.3455 1.3455 1.3455
AA34.999 AA34.999 AA34.999 AA34.999
11.2 11.2 11.2 11.2
11.2BBB 11.2BBB 11.2BBB 11.2BBB
45.9000 45.9 45.9 45.9
900 900 900 900
A.10 A.10 A.10 A.1
Z.0 Z.0 Z.0 Z
0.000006 6e-006 0.000006 0.000006

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-23 : 15:30:34
So it seems there might be more test cases you haven't told us, so right now you are the only one who known which suits your the best.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-25 : 10:11:30
One more value pattern to consider:

SELECT 'ABC0.0' UNION ALL
Go to Top of Page
   

- Advertisement -