A workaround for the PATINDEX issue appears to register a custom function in the dialect as follows:

public class MyDialect extends SQLServer2008Dialect {
  public MyDialect() {
    registerFunction( "indexof", new SQLFunctionTemplate( StandardBasicTypes.LONG, "patindex(?1, ?2)" ) );
  }
}

Then change the query order by as:

query.orderBy(cb.desc(cb.substring(orderPath, cb.function("INDEXOF", Integer.class, cb.literal("%[0-9]%"), orderPath), cb.length(orderPath))));

This workaround appears to solve the SQL error and makes sure that all parameters are rendered in the substring() call. This however still forces SQL Server users of the API to provide a length parameter to the substring() call still.