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 |
|
hasanali00
Posting Yak Master
207 Posts |
 Posted - 2005-10-17 : 12:00:13
|
| Here goes another problem.... I am having to use alot of SQL in my current job and I am not good at it. SO I need your help.I have been given a Product table whoes all column types are varchar(8000)One of the column is Price and other is DecimalPosition. Price column includes price without any decimal place and the data in DecimlaPosition column determins where the decimal should be placed.So for instance, if the Price column includes '1000' and DecimalPosision includes '2' >> then it means that the actual price for this product is '10.00' and NOT '1000'. Similarly, if the DecimalPosision includes '3' >> then it means that the actual price for this product is '1.000' and NOT '1000'My question is that when I am getting the price for a product from this table, how can I get the price in the correct format, e..g like '10.00' and not '1000'kind regards |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-17 : 12:17:18
|
reformat the table  this kind of storage is plainly stupid...select left(Price, DecimalPosition) + '.' + right(Price, DecimalPosition)from tableGo with the flow & have fun! Else fight the flow  |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-17 : 13:10:06
|
| Agreed - highly inefficient storage design.However, if you want to use Spirit1's solution you'll need a slight modification:select left(Price, len(price)-DecimalPosition) + '.' + right(Price, DecimalPosition)Be One with the OptimizerTG |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-17 : 13:13:22
|
| ups... you're right... my bad... Go with the flow & have fun! Else fight the flow |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-17 : 13:36:21
|
I guess techinically the storage of these values isn't inefficient. In fact it should take less disc space than one numeric(38,15) column assuming the values are actual numbers and not long strings of text. Of course using the stored values as numbers is the inefficient part. You'll always have to do conversions on string manipulations which is very expensive. The code will be ugly too when you take into account validating the values before you try to convert them to numbers.hmmm, can you tell I'm not too busy today  Be One with the OptimizerTG |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-18 : 00:34:55
|
| >>I have been given a Product table whoes all column types are varchar(8000)Why?Use proper data types to avoid unnecessary conversionsMadhivananFailing to plan is Planning to fail |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-10-18 : 03:24:19
|
| It may also be worth considering something like SELECT CAST(PRICE AS int) * POWER(10.000, -1 * DecimalPosition)Though this is probably less efficient than Spirit/TG's solution. It also has the extremely irritating quirk that it will only show the number of decimal places that are in the 10 in the POWER function, so the code above will only give 3 decimal places steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
|
|
|
|
|
Converting 1000 into 10.00