Firebird 2 has a SUBSTRING() builtin, expose it thru a function

3 files changed, 17 insertions, 1 deletions

diff --git a/CHANGES b/CHANGES
index a045b0bf5..fc2c38b0e 100644
--- a/CHANGES
+++ b/CHANGES
@@ -35,7 +35,8 @@ CHANGES
             "AutoTranslat=%s" % odbc_autotranslate
       [ticket:1005]
 
-
+- firebird
+    - Handle the "SUBSTRING(:string FROM :start FOR :length)" builtin.
 
 
 0.4.5
diff --git a/lib/sqlalchemy/databases/firebird.py b/lib/sqlalchemy/databases/firebird.py
index 11a88bf18..5e1dd72bb 100644
--- a/lib/sqlalchemy/databases/firebird.py
+++ b/lib/sqlalchemy/databases/firebird.py
@@ -547,6 +547,15 @@ class FBDialect(default.DefaultDialect):
         connection.commit(True)
 
 
+def _substring(s, start, length=None):
+    "Helper function to handle Firebird 2 SUBSTRING builtin"
+
+    if length is None:
+        return "SUBSTRING(%s FROM %s)" % (s, start)
+    else:
+        return "SUBSTRING(%s FROM %s FOR %s)" % (s, start, length)
+
+
 class FBCompiler(sql.compiler.DefaultCompiler):
     """Firebird specific idiosincrasies"""
 
@@ -564,6 +573,9 @@ class FBCompiler(sql.compiler.DefaultCompiler):
         else:
             return self.process(alias.original, **kwargs)
 
+    functions = sql.compiler.DefaultCompiler.functions.copy()
+    functions['substring'] = _substring
+
     def function_argspec(self, func):
         if func.clauses:
             return self.process(func.clause_expr)
diff --git a/test/dialect/firebird.py b/test/dialect/firebird.py
index fe4161a1b..f929443fd 100644
--- a/test/dialect/firebird.py
+++ b/test/dialect/firebird.py
@@ -85,6 +85,9 @@ class CompileTest(TestBase, AssertsCompiledSQL):
         t = Table('sometable', m, Column('col1', Integer), Column('col2', Integer))
         self.assert_compile(select([func.max(t.c.col1)]), "SELECT max(sometable.col1) AS max_1 FROM sometable")
 
+    def test_substring(self):
+        self.assert_compile(func.substring('abc', 1, 2), "SUBSTRING(:substring_1 FROM :substring_2 FOR :substring_3)")
+        self.assert_compile(func.substring('abc', 1), "SUBSTRING(:substring_1 FROM :substring_2)")
 
 class MiscFBTests(TestBase):