I'm developing a database with SQL Server 2012 SP2.
I have a table with a NVARCHAR(20) column, and it will have numbers: "000001", "000002", etc.
I need to get the greatest value in that column and convert it to int. How can I do it?
I have found that I can convert a nvarchar to int with this sql sentence:
SELECT CAST(YourVarcharCol AS INT) FROM Table
If your numbers are padded like in the example given and all have the same width, you can just sort them alphanumerically and then cast the max-value to
BIGINT (depending on your numbers range).
If there are very many rows it was much faster, especially if there is an index on this column...
SELECT TOP 1 * FROM YourTable ORDER BY NumberColumn DESC
or, if you need the max-value only:
SELECT MAX(NumberColumn) FROM YourTable
If you have to deal with negative numbers or differently padded numbers you have to cast them first
SELECT TOP 1 * FROM YourTable ORDER BY CAST(NumberColumn AS INT) DESC
SELECT MAX(CAST(NumberColumn AS INT)) FROM YourTable