title: MySQL - creating a user-defined function for a custom sort
body: <p>I'm working with a large set of legacy data (converted from a flat-file db), where a field is formatted as the last 2 digits of the year the record was entered, followed by a 4 digit increment...</p>
  
  <p>e.g., the third record created in 1998 would be "980003", and the eleventh record created in 2004 would be "040011".</p>
  
  <p>i can not change these values - they exist through their company, are registered with the state, clients, etc.  I know it'd be great to separate out the year and the rest of it into separate columns, but that's not possible.  i can't even really do it "internally" since each row has about 300 fields that are all sortable, and they're very used to working with this field as a record identifier.</p>
  
  <p>so i'm trying to implement a MySQL UDF (for the first time) to sort.  The query executes successfully, and it allows me to "select whatever from table order by custom_sort(whatever)", but the order is not what i'd expect.</p>
  
  <p>Here's what I'm using:</p>
  
  <pre><code>DELIMITER //
  
  CREATE FUNCTION custom_sort(id VARCHAR(8))
      RETURNS INT
      READS SQL DATA
      DETERMINISTIC
      BEGIN
          DECLARE year VARCHAR(2);
          DECLARE balance VARCHAR(6);
          DECLARE stringValue VARCHAR(8);
          SET year = SUBSTRING(0, 2,  id);
          SET balance = SUBSTRING(2, 6, id);
          IF(year &lt;= 96) THEN
              SET stringValue = CONCAT('20', year, balance);
          ELSE
              SET stringValue = CONCAT('19', year, balance);
          END IF;
          RETURN CAST(stringValue as UNSIGNED);
      END//
  </code></pre>
  
  <p>The records only go back to 96 (thus the arbitrary "if first 2 characters are less than 96, prepend '20' otherwise prepend '19').  I'm not thrilled with this bit, but don't believe that's where the core problem is.</p>
  
  <p>To throw another wrench in the works, it turns out that 1996 and 1997 are both 5 digits, following the same pattern described above but instead of a 4 digit increment, it's a 3 digit increment.  Again, I suspect this will be a problem, but is not the core problem.</p>
  
  <p>An example of the returns I'm getting with this custom_sort:</p>
  
  <pre><code>001471
  051047
  080628
  040285
  110877
  020867
  090744
  001537
  051111
  080692
  040349
  110941
  020931
  090808
  001603
  051175
  </code></pre>
  
  <p>I really have no idea what I'm doing here and have never used MySQL for a UDF like this - any help would be appreciated.</p>
  
  <p>TYIA</p>
  
  <p>/EDIT typo</p>
  
  <p>/EDIT 2 concat needed "year" value added - still getting same results</p>