How to implement ORDER BY SUBSTRING in db_query I am migrating comments from a Drupal 6 site into Drupal 7 - but I am importing into the Reply module https://drupal.org/project/reply because the comments were on a Profile node in Drupal 6 but now I'm using Profile2 entities in D7.

I am trying to connect to my legacy database and so far my query is like 

$query = $this-&gt;sourceDbConnection()
  -&gt;select('node', 'n')
  -&gt;fields('n', array('nid', 'title', 'uid',))
  -&gt;fields('c', array('cid', 'pid', 'nid',  'uid', 'subject',
    'comment', 'hostname', 'timestamp', 'status', 'format',
    'thread', 'name', 'mail', 'homepage',
));
$query-&gt;innerJoin('comments', 'c', '(n.nid=c.nid)');
$query-&gt;where('(n.type=:type)', array(':type' =&gt; $this-&gt;LEGACY_CONTENT_TYPE));


However, I wish to add an ORDER BY to the query similar to how it is used in the comment_render function https://api.drupal.org/api/drupal/modules%21comment%21comment.module/function/comment_render/6 i.e. ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))
and I don't know how to do this.
SelectQuery::orderBy() isn't particularly picky, you can just tack this on to the end of your code:

$query-&gt;orderBy('SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))');


With the rest of your code that produces

SELECT n.nid AS nid, n.title AS title, n.uid AS uid, c.cid AS cid, c.pid AS pid, c.nid AS c_nid, c.uid AS c_uid, c.subject AS subject, c.comment AS comment, c.hostname AS hostname, c.timestamp AS timestamp, c.status AS status, c.format AS format, c.thread AS thread, c.name AS name, c.mail AS mail, c.homepage AS homepage
FROM 
{node} n
INNER JOIN {comments} c ON (n.nid=c.nid)
WHERE  ((n.type=:type)) 
ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) ASC