title: MySQL length() not giving correct number of characters
body: <p>I have a table with 8 columns, all varchar (24, 24, 24, 5, 5, 255, 255, 255).</p>
  
  <p>The three important columns are:</p>
  
  <pre><code>icd10code (varchar 24)    
  icd10codedot (varchar 24)   
  icd10length (varchar 5)
  </code></pre>
  
  <p>I wish to convert one column that looks like this - "ABCDEF" to this - "ABC.DEF".</p>
  
  <p>Here is the MySQL query used, and it worked perfectly!</p>
  
  <pre><code>UPDATE icd10
   SET icd10codedot =
   CONCAT (     LEFT(icd10code, 3) ,
                "." ,
                RIGHT( icd10code, ( LENGTH(icd10code)-3 )
                      )
          );
  </code></pre>
  
  <p>All 91,000 rows came out nice EXCEPT there was a trailing "." at the end of those entries that only had 3 characters - like this: "ABC.".
  I don't want that dot at the end.</p>
  
  <p>So I tried this:</p>
  
  <pre><code>UPDATE icd10 SET icd10codedot = LEFT(icd10codedot, 3) WHERE LENGTH(icd10codedot)=4;
  </code></pre>
  
  <p>and this:</p>
  
  <pre><code>UPDATE icd10 SET icd10codedot = LEFT(icd10code, 3) WHERE LENGTH(icd10code)=3;
  </code></pre>
  
  <p>and this:</p>
  
  <pre><code>UPDATE icd10 SET icd10codedot = LEFT(icd10code, 3) WHERE LENGTH(icd10code)=3;
  </code></pre>
  
  <p>and none of them worked (a bunch of other combinations were also run, but none worked).</p>
  
  <p>So to see how things were doing in the code, I made a column called "icd10length" varchar int and did this query:</p>
  
  <pre><code>UPDATE icd10 SET icd10length = LENGTH(icd10code);
  </code></pre>
  
  <p>and all I got was 91,000 "7"s lined up on the right of the column.</p>
  
  <p>I changed the column from INT to VARCHAR 5, and reran the query and got 91000 "7"'s lined up on the RIGHT of the column ( :-) ).</p>
  
  <p>So two questions:</p>
  
  <ol>
  <li><p>How can I get the correct length of the variable icd10code or icd10codedot into a column?</p></li>
  <li><p>Do you have a slick way of clipping the last character of a string that is 4 characters long?</p></li>
  </ol>
  
  <p>Thanks very much again!</p>
  
  <p>PS - I tried the initial query in PHP and it crashed - timing out after 300 seconds. Found on the web that you should always do things in MySQL whenever possible. </p>
  
  <p>PSS - I'm doing all of this via phpMyAdmin.</p>