>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.

Don't expect a good performance if you plan to use these functions on a big table.