title: Formatting Numbers by padding with leading zeros in SQL Server
body: <p>We have an old SQL table that was used by SQL Server 2000 for close to 10 years.</p>
  
  <p>In it, our employee badge numbers are stored as <code>char(6)</code> from <code>000001</code> to <code>999999</code>.</p>
  
  <p>I am writing a web application now, and I need to store employee badge numbers.</p>
  
  <p>In my new table, I could take the short cut and copy the old table, but I am hoping for better data transfer, smaller size, etc, by simply storing the <code>int</code> values from <code>1</code> to <code>999999</code>.</p>
  
  <p>In C#, I can quickly format an <code>int</code> value for the badge number using</p>
  
  <pre><code>public static string GetBadgeString(int badgeNum) {
    return string.Format("{0:000000}", badgeNum);
    // alternate
    // return string.Format("{0:d6}", badgeNum);
  }
  </code></pre>
  
  <p>How would I modify this simple SQL query to format the returned value as well?</p>
  
  <pre><code>SELECT EmployeeID
  FROM dbo.RequestItems
  WHERE ID=0
  </code></pre>
  
  <p>If <code>EmployeeID</code> is 7135, this query should return <code>007135</code>.</p>