title: SQL Server - Create a custom auto-increment field
body: <p>I am trying to produce a custom auto-increment functionality in sql. my custom auto-incerement ID should be like below...</p>
  
  <pre><code>S1501.001
  </code></pre>
  
  <p>"S" is for Supplier's name first letter.</p>
  
  <p>"15" is for this year's last 2 digits.</p>
  
  <p>"01" is today's month</p>
  
  <p>"." will always be there </p>
  
  <p>"001" is my incrementer.</p>
  
  <p>the counter will go on like below</p>
  
  <pre><code>S1501.001
  S1501.002
  S1501.003
  S1501.004
  </code></pre>
  
  <p>Firstly, I have to find the "S1501." and find the ID with highest digits at the end. I can create a new "S1501.005". How can I do this?</p>
  
  <p>I have done something but didnt work.</p>
  
  <pre><code>SELECT TOP 1 (SELECT SUBSTRING('S1501.001', 7,3)),* 
  FROM LG_001_01_SERILOTN 
  WHERE  CODE LIKE SUBSTRING('S1501.001', 1,6)+'%'
  ORDER BY (SELECT SUBSTRING('S1501.001', 7,3)) DESC
  </code></pre>