title: Better techniques for trimming leading zeros in SQL Server?
body: <p>I've been using <a href="https://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement">this</a> for some time:</p>
  
  <pre><code>SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))
  </code></pre>
  
  <p>However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match.</p>
  
  <p>An alternative technique I've seen is to use <code>TRIM</code>:</p>
  
  <pre><code>REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0')
  </code></pre>
  
  <p>This has a problem if there are embedded spaces, because they will be turned into "0"s when the spaces are turned back into "0"s.</p>
  
  <p>I'm trying to avoid a scalar UDF.  I've found a lot of performance problems with UDFs in SQL Server 2005.</p>