4

I'm using SQL Server 2008.

I have a table with a column of type VarChar. It is currently filled with strings which actually represent numbers. Unfortunately, the column must remain VarChar ('1', '2' ... , '1000' ).

I wish to query that field for the max number value but since this is a VarChar I get the Lexicographical max and not the natural order max.

I thought I'll try and solve this by using the COLLATE clause for that query and change to a collation that provides numerical natural order like in this link

  1. Is there a collation like this for SQL Server 2008 and if so, what should be the query?
  2. If a collation like this exists should I use this method or should I use cast?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Oren
  • 2,462
  • 3
  • 18
  • 16
  • No, there is no such "magic" collation. If it's stored as a string - it will be sorted as a string. Period. If it's a number, it should be **stored** as a number .... – marc_s Jun 05 '13 at 09:27

3 Answers3

2

Just a note, a third option is to use a persisted computed column - which is the value cast to a numeric

That way it is only cast once - on create or update

-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
[myString] [varchar] NULL,
[myInt] AS (cast(myString as int)) PERSISTED
)
GO

It's then ok to create an index on the computed column.

jenson-button-event
  • 18,101
  • 11
  • 89
  • 155
2

If all values are numeric, you can simply order by the value converted to an int without requiring a change to the collation or table structure;

SELECT * FROM test ORDER BY CONVERT(INT, value);

An SQLfiddle to test with.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
2

There is no collation option that will perform a natural sort on numbers.

You can however, cast the value as an integer if all the values are expected to be integers.

Try something like

SELECT MAX(CAST (Value as int))
FROM MyTable
Kami
  • 19,134
  • 4
  • 51
  • 63
  • Thank you for answering the question! Can you explain why there is no collation for this? – Oren Jun 05 '13 at 11:35
  • @Oren Collations exist to provide support for different languages and thus work on characters rather than numbers. You simply need to tell SQL something is a number to activate the number sorting algorithms. Things get complicated when you have a mixture of letters and numbers that need natural sorting, but lets not go there. – Kami Jun 05 '13 at 12:32