title: MySQL Left pad and update
body: <p>I need to left pad a field and then save the result as a new account number. I'm trying to figure out the best way to go about this and am having trouble finding solutions.</p>
  
  <p>So far I pass the necessary information from C# into a stored procedure. </p>
  
  <pre><code>using (var connectionCommand = new SqlCommand("updateNumbers", conn) {CommandType = CommandType.StoredProcedure}) 
  
          //add input parameters
          command.Parameters.Add(new SqlParameter("@proc_dateStart", startDate));
          command.Parameters.Add(new SqlParameter("@proc_dateEnd", endDate));
          command.Parameters.Add(new SqlParameter("@proc_accountNumber", _accountNumber));
  </code></pre>
  
  <p>Currently I think replicate is the best way to go about this. However I'm having trouble planning for variation in user input. This needs to support padding between 2 to 6 digits. So an example would be:</p>
  
  <p>Input: 1234
  Output: 001234 or 0000001234</p>
  
  <p>I can easily enough account for the 0 being another number, but it seems like replicate relies on a fixed length - field length.</p>
  
  <pre><code>    UPDATE [StatementItem] 
  SET Account = replicate(@proc_digit, 10 - len(Account) + cast(Account as varchar) as newAccountNumber)
  WHERE Account = accountNumber 
  AND TransactionDate BETWEEN proc_dateStart AND proc_dateEnd;
  </code></pre>
  
  <p>Any suggestions? There must be a better way to go about this.</p>