sql - Next biggest value or next unique value in VARCHAR field -

i've run rather unique request in system work on that's running on t-sql db. have table looks in basic form:

create table merchants (     id          int not null identity(1,1) primary key,     partnerid   int,     externalref varchar(10),     name        varchar(max) ) 

externalref varchar(10) field in carry reference number provided 1 of number of external parties. therefor unique within scope defined partnerid value.

the problem i'm having 1 partner doesn't have values provide , wants generate values them. that's easy enough, use incrementing integer values , convert them varchar. before assigning new value select max(externalref) partnerid = @partnerid, convert integer, add 1 value , convert varchar. in fact, dbms of conversion implicitly , should work fine:

select @newref = max(externalref)+1 merchants partnerid = @partnerid 

but concern is, if 1 of values partner alphanumeric value , gets returned select max(externalref) call? above approach fail in ugly way , i'm not aware of way "increment" alphanumeric string, whatever means.

so need way come unique varchar(10) value not exist in table. generate random value, check if in table , repeat if i'm not mad approach either. if there use in where clause limit results values numeric only, have worked can't think of elegant way that. like:

select @newref = coalesce(max(externalref), 0)+1 merchants partnerid = @partnerid     , externref numeric 

would have suggestions on how can generate "next biggest" varchar value on field or unique value without taking processing?

oh, i've figured out. turns out imagined "is numeric" condition wasn't far fetched. of sql server 2008 there isnumeric function. varchar representation of next biggest integer value in varchar field, here's i'm doing now:

select @newref = coalesce(max(externalref), 0)+1 merchants partnerid = @partnerid     , isnumeric(externref) = 1