VansFannel VansFannel - 2 months ago 13
SQL Question

Get the greatest value in a nvarchar column

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


But I don't know how can I get the max value in that column because the numbers are nvarchar.

UPDATE:


By the way, this column is NVARCHAR because I need to store text on it. I'm testing my solution and I need to store ONLY numbers to test it.

Answer

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 INT or 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...

Something like

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

or

SELECT MAX(CAST(NumberColumn AS INT)) FROM YourTable

Please note:

  • If you've got very many rows, the second might get rather slow. Read about sargable
  • If your NumberColumn might include invalid values, you have to check, Read about ISNUMERIC().
  • The best solution - in any case - was to use an indexed numeric column to store these values
Comments