Query for max record

[bjr149]

I have a list like this.

RC1
RC1001
RC999

When i run a MAX function it returns me the RC999 record when it should be returning me the 1001 records. Is there a way to run the Max function and skip the first 2 letters of the record?

 

[bborissov]

DECLARE @Test Table (MyField varchar(10))
INSERT INTO @test VALUES('RC1')
INSERT INTO @test VALUES('RC1001')
INSERT INTO @test VALUES('RC999')

SELECT MAX(CONVERT(int, SUBSTRING(MyField,3,500))) AS test
       FROM @test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[bjr149]

Ok almost there. This works fine but for another record type i have things like this....

PR13023
PR10432-V2
PR13213-V6

Can i re-convert back??