From 5f45d2ae803911712b11f1219dba7a29ace4d8f2 Mon Sep 17 00:00:00 2001
From: stronk7 <stronk7>
Date: Sat, 9 Sep 2006 13:51:40 +0000
Subject: [PATCH] New function sql_order_by_text() to automatically build
 custom expresions to being used in ORDER BY clauses with TEXT columns.

---
 lib/dmllib.php | 25 +++++++++++++++++++++++++
 1 file changed, 25 insertions(+)

diff --git a/lib/dmllib.php b/lib/dmllib.php
index f8443e253b..3d24f3a408 100644
--- a/lib/dmllib.php
+++ b/lib/dmllib.php
@@ -1312,6 +1312,31 @@ function sql_as() {
     }
 }
 
+/**
+ * Returns the SQL text to be used to order by one TEXT (clob) column, because
+ * some RDBMS doesn't support direct ordering of such fields. 
+ * Note that the use or queries being ordered by TEXT columns must be minimised,
+ * because it's really slooooooow.
+ * @param string fieldname the name of the TEXT field we need to order by
+ * @param string number of chars to use for the ordering (defaults to 32)
+ * @return string the piece of SQL code to be used in your statement.
+ */
+function sql_order_by_text($fieldname, $numchars=32) {
+
+    global $CFG;
+
+    switch ($CFG->dbtype) {
+        case 'mssql':
+            return 'CONVERT(varchar, ' . $fieldname . ', ' . $numchars . ')';
+            break;
+        case 'oci8po':
+            return 'dbms_lob.substr(' . $fieldname . ', ' . $numchars . ',1)';
+            break;
+        default:
+            return $fieldname;
+    }
+}
+
 /** 
  * Prepare a SQL WHERE clause to select records where the given fields match the given values.
  * 
-- 
2.39.5