SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Keep only the meaningful part of decimal value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/21/2013 :  15:12:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 03/21/2013 :  15:26:59  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Italy
232 Posts

Posted - 03/21/2013 :  15:36:59  Show Profile  Reply with Quote
Thank you Yosiasz. Maybe a simple CAST to float could works.

Luigi
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 03/21/2013 :  17:05:51  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Italy
232 Posts

Posted - 03/22/2013 :  04:06:53  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 03/22/2013 :  04:38:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 03/22/2013 :  05:25:22  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 03/22/2013 :  06:58:26  Show Profile  Reply with Quote
IsNumeric, right, thank you Bandi.

Luigi
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 03/22/2013 :  09:39:11  Show Profile  Reply with Quote
quote:
Originally posted by Ciupaz

IsNumeric, right, thank you Bandi.
Luigi

Welcome Ciupaz

--
Chandu
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/22/2013 :  11:12:20  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 03/22/2013 :  19:20:48  Show Profile  Reply with Quote
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

Italy
232 Posts

Posted - 03/23/2013 :  06:14:37  Show Profile  Reply with Quote
It works perfectly. Thank you very much Scott.

Luigi
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 03/23/2013 :  09:44:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 03/23/2013 12:36:25
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
232 Posts

Posted - 03/23/2013 :  10:39:55  Show Profile  Reply with Quote
Great summary Swe!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 03/23/2013 :  12:36:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Italy
232 Posts

Posted - 03/23/2013 :  15:13:08  Show Profile  Reply with Quote
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

Sweden
30250 Posts

Posted - 03/23/2013 :  15:30:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 03/25/2013 :  10:11:30  Show Profile  Reply with Quote
One more value pattern to consider:

SELECT 'ABC0.0' UNION ALL
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000