1

Before anyone yells, yes I know its stupid that there are numbers stored as varchars in the database I am referencing but there isn't anything I can do about it. Basically I have a column that is mostly integers stored as varchar but there are some values with decimals I need to preserve while converting the whole column into some numeric form that I can sort properly later. If I just select the data as is it sorts it like: 1, 10, 11, 12, 13, 2, 2.1, 3 And I need it to be 1, 2, 2.1, 3, 10, 11, 12, 13 but I need to keep the differentiation between 2 and 2.1 or 2.01. There shouldn't ever be a number with more than 2 digits before or after the decimal places. I tried Convert(Decimal, column1) AS 'Column_Name' but that made all the 2.1's into just 2 so had 1, 2, 2, 3, etc. Thoughts?

mcunkelman
  • 21
  • 6

1 Answers1

0

Convert(Decimal, column1) is the same as Convert(Decimal(M,0), column1), where M is an operating system dependent number, however the 0 means that no digits right to the decimal point will be preserved. Use Convert(Decimal(X,2), column1) instead, where X is a sufficiently high number to handle the numbers in the column.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Convert(Decimal(6,3), column1) still just gives the same repeating integers 1,2,2,3 instead of the 1,2,2.1,3 I am after... Any other thoughts? – mcunkelman Oct 14 '15 at 14:43
  • Pls set up an sqlfiddle.com example with sample data and the query you tried and then we can check what goes wrong. – Shadow Oct 14 '15 at 16:51