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.
| Author |
Topic |
|
Pirre0001
Starting Member
19 Posts |
 Posted - 2014-03-10 : 16:38:57
|
| Most values ??in our database is saved by 6 decimal places, whether the value has no values ??in decimal position. Field type is of type nvarchar. How do I remove these "laggards spirit" in the best and smartest way.Ex:100.000000 will be 100100.001000 will be 100.001100.000001 will be be 100.000001100.100000 will be be 100.1and so on... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-10 : 17:12:01
|
If you are on SQL 2012 or later, use FORMAT function like in this example:DECLARE @x NVARCHAR(50) = '100.100000 ';select FORMAT(CAST(@x AS DECIMAL(19,10)),'###.######','en-us'); If you are on an earlier version, you have to use some clever combination of string functions. But before we (you/me/others) try to be clever, are you lucky enough to be on SQL 2012? |
 |
|
|
Pirre0001
Starting Member
19 Posts |
Posted - 2014-03-10 : 17:26:48
|
We are on a SQL Server 2008 R2...quote:
Originally posted by James K
If you are on SQL 2012 or later, use FORMAT function like in this example:DECLARE @x NVARCHAR(50) = '100.100000 ';select FORMAT(CAST(@x AS DECIMAL(19,10)),'###.######','en-us'); If you are on an earlier version, you have to use some clever combination of string functions. But before we (you/me/others) try to be clever, are you lucky enough to be on SQL 2012?
|
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-10 : 19:23:00
|
Here is one way to do this on 2008R2. There might be other/easier ways. This is not yet perfect nor does it work correctly in some edge cases e.g. it does not cut off the trailing decimal point, if there is no decimal point at all, it cuts off significant zeros etc - so this is just something to get you started or to motivate some brilliant minds to post elegant solutions.DECLARE @x NVARCHAR(50) = '170.00001';SELECT REVERSE(STUFF(REVERSE(@x),1,PATINDEX('%[^0]%',REVERSE(@x))-1,'')); |
|
|
|
|
|
|
|
|
Remove trailing zeros from text field?