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.45612.566900 -> 12.566912.0 -> 1212.3456 -> 12.3456and so on.I'm using T-SQL for SQL Server 2008R2.Thanks in advance. Luigi |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
|
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 |
|
|
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 |
|
|
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 |
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-03-22 : 04:38:46
|
For example, I have these values:1.3455000AA34.99911.211.2BBB45.9000and I have to convert only the numeric values, so I get: 1.3455AA34.99911.211.2BBB45.9 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-22 : 05:25:22
|
DECLARE @tabDec TABLE (col1 VARCHAR(25))INSERT INTO @tabDecSELECT '1.3455000' union allSELECT 'AA34.999' union allSELECT '11.2' union allSELECT '11.2BBB' union allSELECT '45.9000'/*and I have to convert only the numeric values, so I get: 1.3455AA34.99911.211.2BBB45.9*/select CASE WHEN ISNUMERIC(col1) = 1 THEN CAST( cast(col1 as float) AS VARCHAR(25)) ELSE col1 ENDFROM @tabDec--Chandu |
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-03-22 : 06:58:26
|
IsNumeric, right, thank you Bandi. Luigi |
|
|
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 |
|
|
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 |
|
|
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) ENDFROM @tabDec |
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-03-23 : 06:14:37
|
It works perfectly. Thank you very much Scott.Luigi |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-03-23 : 09:44:34
|
[code]DECLARE @TabDec TABLE ( Col1 VARCHAR(25) )INSERT @TabDecSELECT '1.3455000' UNION ALLSELECT 'AA34.999' UNION ALLSELECT '11.2' UNION ALLSELECT '11.2BBB' UNION ALLSELECT '45.9000' UNION ALLSELECT '900' UNION ALLSELECT 'A.10' UNION ALLSELECT 'Z.0' UNION ALLSELECT '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 SwePesoFROM @TabDec[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-03-23 : 10:39:55
|
Great summary Swe! |
|
|
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" |
|
|
Ciupaz
Posting Yak Master
232 Posts |
Posted - 2013-03-23 : 15:13:08
|
Yes, you're right. Here the results:Original Chandu Scott SwePeso1.3455000 1.3455 1.3455 1.3455AA34.999 AA34.999 AA34.999 AA34.99911.2 11.2 11.2 11.211.2BBB 11.2BBB 11.2BBB 11.2BBB45.9000 45.9 45.9 45.9900 900 900 900A.10 A.10 A.10 A.1Z.0 Z.0 Z.0 Z0.000006 6e-006 0.000006 0.000006 |
|
|
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" |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-03-25 : 10:11:30
|
One more value pattern to consider:SELECT 'ABC0.0' UNION ALL |
|
|
|