Refs #28643 -- Added Ord, Chr, Left, and Right database functions.

8 files changed, 262 insertions, 6 deletions

diff --git a/django/db/models/functions/__init__.py b/django/db/models/functions/__init__.py
index 0815628dd0..2dfb62769f 100644
--- a/django/db/models/functions/__init__.py
+++ b/django/db/models/functions/__init__.py
@@ -6,7 +6,8 @@ from .datetime import (
     TruncQuarter, TruncSecond, TruncTime, TruncWeek, TruncYear,
 )
 from .text import (
-    Concat, ConcatPair, Length, Lower, Replace, StrIndex, Substr, Upper,
+    Chr, Concat, ConcatPair, Left, Length, Lower, Ord, Replace, Right,
+    StrIndex, Substr, Upper,
 )
 from .window import (
     CumeDist, DenseRank, FirstValue, Lag, LastValue, Lead, NthValue, Ntile,
@@ -23,8 +24,8 @@ __all__ = [
     'TruncMinute', 'TruncMonth', 'TruncQuarter', 'TruncSecond', 'TruncTime',
     'TruncWeek', 'TruncYear',
     # text
-    'Concat', 'ConcatPair', 'Length', 'Lower', 'Replace', 'StrIndex', 'Substr',
-    'Upper',
+    'Chr', 'Concat', 'ConcatPair', 'Left', 'Length', 'Lower', 'Ord', 'Replace',
+    'Right', 'StrIndex', 'Substr', 'Upper',
     # window
     'CumeDist', 'DenseRank', 'FirstValue', 'Lag', 'LastValue', 'Lead',
     'NthValue', 'Ntile', 'PercentRank', 'Rank', 'RowNumber',
diff --git a/django/db/models/functions/text.py b/django/db/models/functions/text.py
index 56a2b66ad5..d22928343c 100644
--- a/django/db/models/functions/text.py
+++ b/django/db/models/functions/text.py
@@ -1,7 +1,23 @@
-from django.db.models import Func, Transform, Value, fields
+from django.db.models import Func, IntegerField, Transform, Value, fields
 from django.db.models.functions import Coalesce
 
 
+class Chr(Transform):
+    function = 'CHR'
+    lookup_name = 'chr'
+
+    def as_mysql(self, compiler, connection):
+        return super().as_sql(
+            compiler, connection, function='CHAR', template='%(function)s(%(expressions)s USING utf16)'
+        )
+
+    def as_oracle(self, compiler, connection):
+        return super().as_sql(compiler, connection, template='%(function)s(%(expressions)s USING NCHAR_CS)')
+
+    def as_sqlite(self, compiler, connection, **extra_context):
+        return super().as_sql(compiler, connection, function='CHAR', **extra_context)
+
+
 class ConcatPair(Func):
     """
     Concatenate two arguments together. This is used by `Concat` because not
@@ -55,6 +71,30 @@ class Concat(Func):
         return ConcatPair(expressions[0], self._paired(expressions[1:]))
 
 
+class Left(Func):
+    function = 'LEFT'
+    arity = 2
+
+    def __init__(self, expression, length, **extra):
+        """
+        expression: the name of a field, or an expression returning a string
+        length: the number of characters to return from the start of the string
+        """
+        if not hasattr(length, 'resolve_expression'):
+            if length < 1:
+                raise ValueError("'length' must be greater than 0.")
+        super().__init__(expression, length, **extra)
+
+    def get_substr(self):
+        return Substr(self.source_expressions[0], Value(1), self.source_expressions[1])
+
+    def use_substr(self, compiler, connection, **extra_context):
+        return self.get_substr().as_oracle(compiler, connection, **extra_context)
+
+    as_oracle = use_substr
+    as_sqlite = use_substr
+
+
 class Length(Transform):
     """Return the number of characters in the expression."""
     function = 'LENGTH'
@@ -70,6 +110,18 @@ class Lower(Transform):
     lookup_name = 'lower'
 
 
+class Ord(Transform):
+    function = 'ASCII'
+    lookup_name = 'ord'
+    output_field = IntegerField()
+
+    def as_mysql(self, compiler, connection, **extra_context):
+        return super().as_sql(compiler, connection, function='ORD', **extra_context)
+
+    def as_sqlite(self, compiler, connection, **extra_context):
+        return super().as_sql(compiler, connection, function='UNICODE', **extra_context)
+
+
 class Replace(Func):
     function = 'REPLACE'
 
@@ -77,6 +129,13 @@ class Replace(Func):
         super().__init__(expression, text, replacement, **extra)
 
 
+class Right(Left):
+    function = 'RIGHT'
+
+    def get_substr(self):
+        return Substr(self.source_expressions[0], self.source_expressions[1] * Value(-1))
+
+
 class StrIndex(Func):
     """
     Return a positive integer corresponding to the 1-indexed position of the
diff --git a/docs/ref/models/database-functions.txt b/docs/ref/models/database-functions.txt
index 41e2966629..0030de55b9 100644
--- a/docs/ref/models/database-functions.txt
+++ b/docs/ref/models/database-functions.txt
@@ -685,6 +685,28 @@ that deal with time-parts can be used with ``TimeField``::
 Text functions
 ==============
 
+``Chr``
+-------
+
+.. class:: Chr(expression, **extra)
+
+.. versionadded:: 2.1
+
+Accepts a numeric field or expression and returns the text representation of
+the expression as a single character. It works the same as Python's :func:`chr`
+function.
+
+Like :class:`Length`, it can be registered as a transform on ``IntegerField``.
+The default lookup name is ``chr``.
+
+Usage example::
+
+    >>> from django.db.models.functions import Chr
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> author = Author.objects.filter(name__startswith=Chr(ord('M'))).get()
+    >>> print(author.name)
+    Margaret Smith
+
 ``Concat``
 ----------
 
@@ -716,6 +738,23 @@ Usage example::
     >>> print(author.screen_name)
     Margaret Smith (Maggie)
 
+``Left``
+--------
+
+.. class:: Left(expression, length, **extra)
+
+.. versionadded:: 2.1
+
+Returns the first ``length`` characters of the given text field or expression.
+
+Usage example::
+
+    >>> from django.db.models.functions import Left
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> author = Author.objects.annotate(first_initial=Left('name', 1)).get()
+    >>> print(author.first_initial)
+    M
+
 ``Length``
 ----------
 
@@ -761,6 +800,29 @@ Usage example::
     >>> print(author.name_lower)
     margaret smith
 
+``Ord``
+-------
+
+.. class:: Ord(expression, **extra)
+
+.. versionadded:: 2.1
+
+Accepts a single text field or expression and returns the Unicode code point
+value for the first character of that expression. It works similar to Python's
+:func:`ord` function, but an exception isn't raised if the expression is more
+than one character long.
+
+It can also be registered as a transform as described in :class:`Length`.
+The default lookup name is ``ord``.
+
+Usage example::
+
+    >>> from django.db.models.functions import Ord
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> author = Author.objects.annotate(name_code_point=Ord('name')).get()
+    >>> print(author.name_code_point)
+    77
+
 ``Replace``
 -----------
 
@@ -783,6 +845,23 @@ Usage example::
     >>> Author.objects.values('name')
     <QuerySet [{'name': 'Margareth Johnson'}, {'name': 'Margareth Smith'}]>
 
+``Right``
+---------
+
+.. class:: Right(expression, length, **extra)
+
+.. versionadded:: 2.1
+
+Returns the last ``length`` characters of the given text field or expression.
+
+Usage example::
+
+    >>> from django.db.models.functions import Right
+    >>> Author.objects.create(name='Margaret Smith')
+    >>> author = Author.objects.annotate(last_letter=Right('name', 1)).get()
+    >>> print(author.last_letter)
+    h
+
 ``StrIndex``
 ------------
 
diff --git a/docs/releases/2.1.txt b/docs/releases/2.1.txt
index 414da31109..b1fc5015f6 100644
--- a/docs/releases/2.1.txt
+++ b/docs/releases/2.1.txt
@@ -183,8 +183,12 @@ Models
 * A ``BinaryField`` may now be set to ``editable=True`` if you wish to include
   it in model forms.
 
-* The new :class:`~django.db.models.functions.Replace` database function
-  replaces strings in an expression.
+* A number of new text database functions are added:
+  :class:`~django.db.models.functions.Chr`,
+  :class:`~django.db.models.functions.Ord`,
+  :class:`~django.db.models.functions.Left`,
+  :class:`~django.db.models.functions.Right`, and
+  :class:`~django.db.models.functions.Replace`.
 
 * The new :class:`~django.db.models.functions.TruncWeek` function truncates
   :class:`~django.db.models.DateField` and
diff --git a/tests/db_functions/test_chr.py b/tests/db_functions/test_chr.py
new file mode 100644
index 0000000000..0b4b0cc77c
--- /dev/null
+++ b/tests/db_functions/test_chr.py
@@ -0,0 +1,32 @@
+from django.db.models import IntegerField
+from django.db.models.functions import Chr, Left, Ord
+from django.test import TestCase
+
+from .models import Author
+
+
+class ChrTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        cls.john = Author.objects.create(name='John Smith', alias='smithj')
+        cls.elena = Author.objects.create(name='Élena Jordan', alias='elena')
+        cls.rhonda = Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(first_initial=Left('name', 1))
+        self.assertCountEqual(authors.filter(first_initial=Chr(ord('J'))), [self.john])
+        self.assertCountEqual(authors.exclude(first_initial=Chr(ord('J'))), [self.elena, self.rhonda])
+
+    def test_non_ascii(self):
+        authors = Author.objects.annotate(first_initial=Left('name', 1))
+        self.assertCountEqual(authors.filter(first_initial=Chr(ord('É'))), [self.elena])
+        self.assertCountEqual(authors.exclude(first_initial=Chr(ord('É'))), [self.john, self.rhonda])
+
+    def test_transform(self):
+        try:
+            IntegerField.register_lookup(Chr)
+            authors = Author.objects.annotate(name_code_point=Ord('name'))
+            self.assertCountEqual(authors.filter(name_code_point__chr=Chr(ord('J'))), [self.john])
+            self.assertCountEqual(authors.exclude(name_code_point__chr=Chr(ord('J'))), [self.elena, self.rhonda])
+        finally:
+            IntegerField._unregister_lookup(Chr)
diff --git a/tests/db_functions/test_left.py b/tests/db_functions/test_left.py
new file mode 100644
index 0000000000..f853ac21ac
--- /dev/null
+++ b/tests/db_functions/test_left.py
@@ -0,0 +1,27 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Left, Lower
+from django.test import TestCase
+
+from .models import Author
+
+
+class LeftTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(name_part=Left('name', 5))
+        self.assertQuerysetEqual(authors.order_by('name'), ['John ', 'Rhond'], lambda a: a.name_part)
+        # If alias is null, set it to the first 2 lower characters of the name.
+        Author.objects.filter(alias__isnull=True).update(alias=Lower(Left('name', 2)))
+        self.assertQuerysetEqual(authors.order_by('name'), ['smithj', 'rh'], lambda a: a.alias)
+
+    def test_invalid_length(self):
+        with self.assertRaisesMessage(ValueError, "'length' must be greater than 0"):
+            Author.objects.annotate(raises=Left('name', 0))
+
+    def test_expressions(self):
+        authors = Author.objects.annotate(name_part=Left('name', Value(3), output_field=CharField()))
+        self.assertQuerysetEqual(authors.order_by('name'), ['Joh', 'Rho'], lambda a: a.name_part)
diff --git a/tests/db_functions/test_ord.py b/tests/db_functions/test_ord.py
new file mode 100644
index 0000000000..93ec38b076
--- /dev/null
+++ b/tests/db_functions/test_ord.py
@@ -0,0 +1,27 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Left, Ord
+from django.test import TestCase
+
+from .models import Author
+
+
+class OrdTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        cls.john = Author.objects.create(name='John Smith', alias='smithj')
+        cls.elena = Author.objects.create(name='Élena Jordan', alias='elena')
+        cls.rhonda = Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(name_part=Ord('name'))
+        self.assertCountEqual(authors.filter(name_part__gt=Ord(Value('John'))), [self.elena, self.rhonda])
+        self.assertCountEqual(authors.exclude(name_part__gt=Ord(Value('John'))), [self.john])
+
+    def test_transform(self):
+        try:
+            CharField.register_lookup(Ord)
+            authors = Author.objects.annotate(first_initial=Left('name', 1))
+            self.assertCountEqual(authors.filter(first_initial__ord=ord('J')), [self.john])
+            self.assertCountEqual(authors.exclude(first_initial__ord=ord('J')), [self.elena, self.rhonda])
+        finally:
+            CharField._unregister_lookup(Ord)
diff --git a/tests/db_functions/test_right.py b/tests/db_functions/test_right.py
new file mode 100644
index 0000000000..b75bfd5155
--- /dev/null
+++ b/tests/db_functions/test_right.py
@@ -0,0 +1,27 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Lower, Right
+from django.test import TestCase
+
+from .models import Author
+
+
+class RightTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(name_part=Right('name', 5))
+        self.assertQuerysetEqual(authors.order_by('name'), ['Smith', 'honda'], lambda a: a.name_part)
+        # If alias is null, set it to the first 2 lower characters of the name.
+        Author.objects.filter(alias__isnull=True).update(alias=Lower(Right('name', 2)))
+        self.assertQuerysetEqual(authors.order_by('name'), ['smithj', 'da'], lambda a: a.alias)
+
+    def test_invalid_length(self):
+        with self.assertRaisesMessage(ValueError, "'length' must be greater than 0"):
+            Author.objects.annotate(raises=Right('name', 0))
+
+    def test_expressions(self):
+        authors = Author.objects.annotate(name_part=Right('name', Value(3), output_field=CharField()))
+        self.assertQuerysetEqual(authors.order_by('name'), ['ith', 'nda'], lambda a: a.name_part)