How to implement MAX function on a text column in SQL Server?

I'm using SQL Server 2005 and have a column that contains serial numbers, which are nvarchar(50).

My problem is selecting max(serial_no) from the table. The serial numbers used to have a length of 7 only but new ones are now 15. Whenever I select the max, I get a result with a length of 7, which means that data is old. I also can't filter it to only select from records which have a length of 15 because then i'll miss some other data on my query.

Old serial numbers look like this...

'SNGD001'

..., and new ones look like this:

'SN14ABCD0000001'

Edit: I tried creating a dummy table without the old serial numbers (5 characters long), and I'm getting correct results.

Solution

As has been mentioned, your question is a bit hard to follow. If the max value could be either one of your old serial numbers or one of your new ones, I believe the following should do the trick:

SELECT MAX(RIGHT('0000000' + REVERSE(LEFT(REVERSE(YourTextColumn), PATINDEX('%[a-z]%', REVERSE(YourTextColumn)) - 1)), 7))
FROM YourTable

It finds the first non numeric character from the right keeping everything to the right of that. It then left zero pads the resulting numeric string to 7 characters and applies the MAX function.