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
If it's not broken, fix it until it is.
My Blog