Here's my final result ( changed the rule about left padding the number with Os after consulting the business side )

First a UDF that will always come in handy

CREATE Function [dbo].[udf_NumbersOnly](@Temp VarChar(8000))
Returns VarChar(1000)
AS
Begin
 
    While PatIndex('%[^0-9]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
 
    Return @Temp
END

GO

Then the UDF I could have never written without your getting my head going in the right direction. Table is Symbology. Column is Symbol
from_date and thru_date determines current rows for each symbol ( no deletions, just 'Thru-dating' )

-- =============================================
CREATE FUNCTION [dbo].[udf_SuggestedSymbol] 
(
	-- Add the parameters for the function here
	@Cntry_Code char(2),
	@effdate datetime 
	
)
RETURNS char(6)
AS
BEGIN
	-- Declare the return variable here
	DECLARE
	 @Result char(6),
	 @MaxNumber int,
	 @SearchSymbol char(6)
	

	-- Add the T-SQL statements to compute the return value here


if @Cntry_code = 'US'

	begin

	;with cte as (
	select symbol, dbo.udf_NumbersOnly(symbol) numberonly  from symbology  
	where Symbol like 'U[0-9]%' 
	and @effdate between from_date and thru_date) 
	select @Maxnumber = MAX(numberonly) from cte

	select @SearchSymbol = 'U'+CAST(@maxnumber+1 as varchar(5))

	while 1=1
		begin
			if not exists(select 1 from Symbology where symbology.symbol = @SearchSymbol)
			break
		else
			begin
			set @MaxNumber = @MaxNumber + 1
			SELECT @SearchSymbol = 'U' + cast(@MaxNumber as varchar(5))
			
			end
		end
		
		set @Result = @SearchSymbol
		
	end

else

	begin

	;with cte as (
		select symbol, dbo.udf_NumbersOnly(symbol) numberonly  from symbology  
		where Symbol like @Cntry_code+'[0-9]%' 
		and @effdate between from_date and thru_date) 
		select @Maxnumber = MAX(numberonly) from cte

		select @SearchSymbol = @Cntry_code+ CAST(@maxnumber+1 as varchar(4))

		while 1=1
			begin
				if not exists(select 1 from Symbology where symbology.symbol = @SearchSymbol)
				break
			else
				begin
				set @MaxNumber =  @MaxNumber + 1
				SELECT @SearchSymbol = @Cntry_code + cast(@MaxNumber as varchar(4))
				
				end
			end
			
			set @Result = @SearchSymbol
		end
		
		
		
		-- Return the result of the function
		RETURN @Result

END

GO

>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