CODEWITHSUNDEEP
sqlsql-server
Molasses
Molasses

Reputation: 699

SQL: Find highest number if its in nvarchar format containing special characters

I need to pull the record containing the highest value, specifically I only need the value from that field. The problem is that the column is nvarchar format that contains a mix of numbers and special characters. The following is just an example:

PK      | Column 2 (nvarchar)
-------------------
1       | .1.1.
2       | .10.1.1
3       | .5.1.7
4       | .4.1.
9       | .10.1.2
15      | .5.1.4

Basically, because of natural sort, the items in column 2 are sorted as strings. So instead of returning the PK for the row containing ".10.1.2" as the highest value i get the PK for the row that contains ".5.1.7" instead.

I attempted to write some functions to do this but it seems what I've written looked way more complicated than it should be. Anyone got something simple or complicated functions are the only way?

I want to make clear that I'm trying to grab the PK of the record that contains the highest Column 2 value.

Upvotes: 0

Views: 1044

Answers (1)

Saad Surya
Saad Surya

Reputation: 505

This query might return what you desire

SELECT MAX(CAST(REPLACE(Column2, '.', '') as INT)) FROM table

Upvotes: 2

Related Questions