title: Sequence number between string in sql
body: <p>I have a stored procedure like this</p>
  
  <pre><code>declare @Tmp varchar(60)
  declare @Hasil varchar(60)
  declare @PFID varchar(50)
  declare @CntPFID int
  declare @PrevPFID varchar(60)
  DECLARE @CheckLetter CHAR(5)
  
  set @PFID = 'PF-FB/ALL/009/MKX/VI/16'
  
  set @CheckLetter = (UPPER(RIGHT((CAST(SUBSTRING(@PFID, 10, 4) AS VARCHAR(5))), 3)))
  
  select @CntPFID = count(pfid) 
  from pf 
  where pfid like '%'+ right(@PFID, 9) 
    and pfid like ''+  left(@PFID, 13) +'%'
  
  if @CntPFID = 1
  begin
      set @Tmp = (UPPER(RIGHT((CAST(SUBSTRING(@PFID, 10, 4) AS VARCHAR(5))), 3))) + '-' + '01'
      set @Hasil = replace(@PFID, replace(@CheckLetter, ' ', ''), @Tmp)
  end
  else 
  begin
      set @Tmp = (UPPER(RIGHT((CAST(SUBSTRING(@PFID, 10, 4) AS VARCHAR(5))), 3))) + '-0' + cast(@CntPFID as varchar(3))
      set @Hasil = replace(@PFID, replace(@CheckLetter, ' ', ''), @Tmp)
  end
  
  select @Hasil 
  </code></pre>
  
  <p>I was stuck when the record data already reach 'PF-FB/ALL/009-09/MKX/VI/16' the next number becoming PF-FB/ALL/009-<strong>010</strong>/MKX/VI/16 (there is three digits = 010) while I want it will be PF-FB/ALL/009-<strong>10</strong>/MKX/VI/16 (2 digits running). </p>
  
  <p>Is there any dynamically way without using condition else if > 9 ... ?</p>