title: Generate a string format based on fixed length and numeric units on each increment
body: <p>I had to write a SP to generate a string with the combination of Country (7 chars) + Province (1 char) + numeric number starts from 01. for eg: JAMAICAK01
  where JAMAICA (Country) , K (Province) and 01 is the numeric number that gets incremented by 1 for each transaction.</p>
  
  <p>The issue I have here is the generated string length max is 10, can be lesser than 10 but not >10.</p>
  
  <p>It should be handled in a way with certain rules like</p>
  
  <ol>
  <li>the combination don't exist </li>
  <li>When the numeric unit changes from tens to hundreds making the string 
  length >10, I need to remove the right chars for eg JAMAICAKKK10 to 
  JAMAICAK10 from the right to make the total max length 10. </li>
  </ol>
  
  <p>In my code below I tried to check if the combination exists and I get the max of it and do the numeric increment from the last one. think it can be done in a better way.</p>
  
  <pre><code>       Declare @Province char(2)
         Declare @Country varchar(10)
         declare @CounProv varchar(10)
         Declare @SimilarCounPRov varchar(max) = '';
         declare @FinalString nvarchar(12)
         declare @s varchar(50)
         declare @s1 varchar(50)
         declare @s2 varchar(50)
  
         Set @Province = LEFT('KINGSTON', 1)           
         Set @Country = LEFT('JAMAICA', 7)   
  
         Set @CounProv = @Country+@Province
  
         Select @SimilarCounPRov = MAX(field1) from dbo.table where field1  
         LIKE '%JAMAICAK%' 
  
         if @SimilarCounPRov IS NOT NULL 
     BEGIN
     Set @s = (select fn_AlphaOnly('%JAMAICAK99%')) -- returns JAMAICAK
  
     Set @s1 = (select fn_NumericOnly('%JAMAICAK99%'))  -- returns 199
  
     set @s2= @s1 +1   -- increment by 1
  
     if len(@FinalString) &gt; 10
        ---- 
         need help here----`
  </code></pre>