how to select and convert char value with decimal point to correct decimal place

[dsd50]

hard to explain... i have a character field with a 'floating' decimal point and need to order it to get the next incremental decimal place but can't get the convert/cast to work after 1 place to the right of decimal. ie) 101.1, 101.2, ... 101.9, 101.10; problem is 101.10 and 101.11 order before 101.2

[Rabbit]

Use the convert function to change the data type of the field to decimal so you can order by it instead of text.

[dsd50]

I have tried converting to decimal but 101.1 and 101.10 both convert to 101.10; 101.2 converts to 101.20; I really need 101.1 to convert to 101.01 but I can't figure out how?

[ck9663]

Are you trying to convert it for consumption or just trying to sort it? If your values are 101.10, 101.11 and 101.2, how do you want your output to look like?

~~ CK

[dsd50]

I am trying to sort it descending to know the next incremental number to the right of decimal place. 101.11 should be the highest so I can increment to 101.12; 101.1 and 101.2 are the first and second entries ... 101.11 is the eleventh entry.

[Rabbit]

That doesn't make any sense.

101.1 is the same thing as 101.10. That's basic math.

I don't know why you think 101.1 = 101.01, where is that even coming from? What kind of math are you using? Or is there some sort of business requirement you have not specified. If you have a special requirement that breaks the laws of math, you need to tell us about it because there's no way for us to know that by 101.1 you mean 101.01. You need to lay out the rules by which you are evaluating your numbers for us to be able to help.

[dsd50]

it is not math. the number to the right of the decimal is a counter. 1,2,3,4... 10, 11, 12 . The entire number and counter is stored in the same character field for some stupid reason; (101.1, 101.2 ... 101.10, 101.11) I inherited this code and would never design it this way. The 'sorting' code worked fine until the counter went to 2 digits, that is why I want 101.1 to really convert to 101.01 and not 101.10

[Rabbit]

In that case, I would use a combination if the INSTR() function to find the location of the "." and the MID() function to separate out the 2 parts of the field. Once they have been separated, you can convert each part to an integer and sort by them.

[ck9663]

Here, try this...

substring(cast((<YourNumber> % 1)  as varchar(25)),3,25)

~~ CK