Glad it helped. I believe in the cte we may want to filter with where ch LIKE 'U%'

>That's really beautiful, Naomi. Dow, Jones, and I all thank you.
>
>
>>Try
>>
>>

>>declare @t table (ch char(6))
>>insert into @t values ('U290'),('U2888&'),('U2345'),('U00001'), ('U12ABC')
>>
>>declare @Param char(2), @MaxNumber int, @ValueToSearch char(6)
>>set @Param = 'US' ;
>>
>>if @Param = 'US'
>>
>>with cte as (select SUBSTRING(ch,2, patindex('%[0-9][^0-9]%', ch + '!')-1) as NumberOnly
>>from @t) 
>>
>>select @MaxNumber = max(case when NumberOnly like '%[^0-9]%' then 0 else CAST(NumberOnly as int) end) 
>>from cte 
>>
>>select @ValueToSearch = 'U' + RIGHT('00000' + LTRIM(@MaxNumber + 1),5) 
>>while 1=1
>>   begin
>>      if not exists(select 1 from @t where ch = @ValueToSearch)
>>         break
>>      else
>>         begin
>>            set @MaxNumber = @MaxNumber + 1
>>            select @ValueToSearch = 'U' + RIGHT('00000' + LTRIM(@MaxNumber + 1),5) 
>>
>>         end
>>   end
>>   
>>select @ValueToSearch

>>
>>Similar solution for the other case.
>>
>>>Have a table with about 25000 rows. Column Symbol char(6).
>>>
>>>I would like a stored procedure that will accept a parameter that will be char(2)
>>>
>>>If @param = 'US'
>>>
>>>of the set of symbols beginning with U and followed by a digit - (UK not a match ) , find the max value of the digits in the string ( possible at least one of chars 2-6 will be alpha and that there may be blanks at the end
>>>
>>>So
>>>U290
>>>U2888&
>>>U2345
>>>U00001
>>>
>>>will get 2888
>>>
>>>Then increment that number by 1 , padleft(5) with 0, test to see if 'U'+that string ( U02889 ) is found in the column and if not return that string
>>>If found, increment number and try again until not match is found
>>>
>>>If @param is char(2) ( or is not US) , do the same thing but matching against first 2 chars, then performing same procedure on remaining 4 characters.
>>>
>>>CZ0001
>>>CZ24&
>>>CZ490#
>>>
>>>returns CZ0490 if there isn't already one in the column, else CZ0491 etc until no match.
>>>
>>>I can probably kludge up something ugly but I'm sure some of the clever TSQLers here can show me something elegant and I get to learn something.
>>>
>>>Thanks in advance