Convert and sort char type to numeric column (1/2,1/4,3/8 ...)

[znet]

Hello,
in databse, a column: DrvSize nvarchar(10), the value like these: 1,2,1/2,1/4,3/8.... I need they are sorted by numeric value as 1/4,3/8,1/2,1,2. Any suggestions. Thanks in advance

 

[gcole]

Use the Order by command and use the cast() funtcion to convert them.

 

[SQLSister]

You have a problem in that you do not have numeric data, so you can't cast the data to numeric because it will not recognize 1/2 as a number.

<rant>If you ever intend to use data as a number, never store it as character data!</rant>

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[vongrunt]

To SQLSister: out of curiosity, how you US/UK folks store measures in database? Ft/in? Lbs/oz?

 

[SQLSister]

Sorry didn't mean to hit enter til I was done. What you will need to do is convert all the fractions to decimals. Then you can use the data correctly. This isn't too bad if you have a limited set of fractions to deal with. I would first create a new field (decimal datatype) to store the data in. Then run an update statement for each decimal conversion you need to do, storing the information in the new field. TRhen finally convert all the whole numbers by updating the field where it is null.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[SQLSister]

vongrunt, I'm sure that there is no standard way to store this information just like not everyone stores dates as dates. If I were storing measures, I would store them in decimals of the main unit I was concerned with. So if I wanted to know about gallons, I would store 1/2 gallon as .5. I always store anything I want to perform calculations on in a numeric form. There is no way I know of to store fractions as anything other than decimals if you want to sort on them or add them up later.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[vongrunt]

I know some people pad decimals for height (6ft 1in = 6.01), which works OK in case of fixed decimal base (12). The same convention is often used for geo-coordinates (bases 60 and 60). But for pure rational numbers with variable base (1/4, 2/3 etc) this obviously doesn't work.

 

[znet]

Thanks everyone,
The column actually is for hand tools drive size(inlcuding inch unit), like 1/2",#5..., so it should not be numetric data type. The difficult point is converting to numetric type.

 

[donutman]

Vongrunt, I have to deal with various measures and so I have a measure field and its numeric value. When something is measured in both lbs and ozs, I convert to the smaller measure so that it can be entered as one number. However, we convert almost all of the ingredients within a recipe to lbs with decimals which took some time for the old bakers to adjust to. Bakery scales (mechanical) had been traditionally set to display lbs and ozs. I use a measure field that makes it possible to convert the exceptions (for computing total weight) where it's more appropriate to tell a baker to use 1 pail or .5 pail of something.
Having said that, I do make extensive use of the 6ft 3in = 6.03 notation for counting items, but this is done at the report level, e.g. if we need 75 cherry sweet rolls that is 2 pans and 5 pieces = 2.05.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]