You have to pad them from the left to the same length to make comparable. Assuming that reference cannot have leading spaces and max len of it is 30 characters

SELECT 
    LTRIM(MAX(RIGHT(REPLICATE(' ',30) + RTRIM(ref),30))) As MaxRef
...

>
>AAAA is greater than ZZZ because it has more characters in it.
>
>My client sees it as being similar to 1111 being bigger than 999.
>
>Matt.
>
>
>>>>
>>How you define "greater" for the reference field? IOW, why AAAA is greater than ZZZ?
>>
>>>
>>>Our tables have a reference field which the user can type into. The field can contain pretty much anything the user can think of e.g.
>>>
>>>AAA
>>>AZ49
>>>Glasgow/01 etc.
>>>
>>>However, the user can also ask the system to calculate the next reference.
>>>
>>>In our system, the string AAAA would be greater than ZZZ, and the reference after AAAA would be AAAB.
>>>
>>>Using MAX() in the above example would return ZZZ, when we would want AAAA returned.
>>>
>>>Is there a SQL statement which would return what we consider to be the maximum reference?
>>>
>>>Matt.