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 |
|
tool
Starting Member
26 Posts |
 Posted - 2004-11-15 : 09:54:15
|
| I have a table with a column of type decimal(12,5). In my query I am casting the column to a varchar but would also like to drop any unnecesary decimal places from the results (e.g. 48932.12000 --> 48932.12). Is there an easy way to accomplish this or should I create a UDF to perform the task?Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-15 : 10:25:41
|
| select left(@s,len(@s)-patindex('%[^0.]%',reverse(@s))+1)that will remove the trailing 0's and also the decimal point if no decimal places.Will have problems if there is no decimal point.oops1000.000will turn into 1 too.Not so neat nowselect replace(left(replace(@s,'.','|.'),len(replace(@s,'.','|.'))-patindex('%[^0.]%',reverse(replace(@s,'.','|.')))+1),'|','')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-15 : 10:30:48
|
| This is a presentation layer issue. do not do this in T-SQL. if you format the data, you are returning a varchar to your client. You are better off returning raw data to your client and allowing it to manipulate it / format it as it needs. when you force varchar's that are pre-formated as output, you are needlessly complicating things on both ends.- Jeff |
|
|
|
|
|
|
|
|
Formatting a decimal