title: SQL Server : function to trim only leading zero's
body: <p>I have to make a function that trims leading zero's. It's however a little more complex than just getting rid of the first zero's. </p>
  
  <p>Few examples and results:</p>
  
  <ul>
  <li>QZ0106 should become: QZ106</li>
  <li>0108 should become: 108</li>
  <li>00000B0108 should become: B0108</li>
  <li>011B%000407A0001! should become: 11B%000407A0001!</li>
  </ul>
  
  <p>I have built a query myself and came up with the following</p>
  
  <pre><code> RETURN LEFT(@input, PATINDEX('%0%', @input)-1) + 
   SUBSTRING(SUBSTRING(@input, PATINDEX('%0%',@input), LEN(@input)), PATINDEX('%[^0]%', SUBSTRING(@input,PATINDEX('%0%',@input),LEN(@input))),len(@input))
  </code></pre>
  
  <p>Now, this works really good to get the above results.</p>
  
  <p><strong>However:</strong> </p>
  
  <p>ZBET108 should stay <strong>ZBET108.</strong> - the above query returns <strong>ZBET18</strong></p>
  
  <p>I can't get it to work. The query should find the first numeric string and trim the leading zero's of that. What it seems to do now is search for the first zero and remove it. </p>
  
  <p>Could anyone assist me in this?</p>
  
  <p>Thanks a lot guys.</p>