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