Text functions

# ======================================================================
# A gallery of string functions, each computed in the database over one book's
# title. They map directly to SQL: `Lower`/`Upper`, `Length`, `Substr`,
# `Left`/`Right`, `LPad`, `Trim`, `Replace`, `Reverse`, `StrIndex` (position),
# `Repeat`, `MD5`/`SHA256` hashing, and `Concat`.
# ======================================================================

# ----------------------------------------------------------------------
# string functions over 'The Hobbit'
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(title="The Hobbit").annotate(
    lower=Lower("title"), upper=Upper("title"), length=Length("title"),
    substr=Substr("title", 1, 3), left=Left("title", 3), right=Right("title", 3),
    lpad=LPad("title", 18, Value("·")), rpad=RPad("title", 18, Value("·")),
    trimmed=Trim(Value("   spaced   ")), ltrim=LTrim(Value("   x")), rtrim=RTrim(Value("x   ")),
    replaced=Replace("title", Value("o"), Value("0")), reversed=Reverse("title"),
    idx=StrIndex("title", Value("Hobbit")), repeat=Repeat(Value("ab"), 3),
    chr=Chr(Value(65)), ord=Ord("title"),
    md5=MD5("title"), sha=SHA256("title"),
    concat=Concat("title", Value(" — "), "author__name"),
)

# SQL:
#   SELECT LOWER("examples_book"."title") AS "lower", UPPER("examples_book"."title") AS "upper", LENGTH("examples_book"."title") AS "length", SUBSTRING("examples_book"."title", 1, 3) AS "substr", LEFT("examples_book"."title", 3) AS "left", RIGHT("examples_book"."title", 3) AS "right", LPAD("examples_book"."title", 18, '·') AS "lpad", TRIM('   spaced   ') AS "trimmed", REPLACE("examples_book"."title", 'o', '0') AS "replaced", REVERSE("examples_book"."title") AS "reversed", STRPOS("examples_book"."title", 'Hobbit') AS "idx", REPEAT('ab', 3) AS "repeat", MD5("examples_book"."title") AS "md5", ENCODE(DIGEST("examples_book"."title", 'sha256'), 'hex') AS "sha", (COALESCE("examples_book"."title", '') || COALESCE((COALESCE(' — ', '') || COALESCE("examples_author"."name", '')), '')) AS "concat", RPAD("examples_book"."title", 18, '·') AS "rpad", LTRIM('   x') AS "ltrim", RTRIM('x   ') AS "rtrim", CHR(65) AS "chr", ASCII("examples_book"."title") AS "ord"
#   FROM "examples_book"
#   INNER JOIN "examples_author" ON ("examples_book"."author_id" = "examples_author"."id")
#   WHERE "examples_book"."title" = 'The Hobbit'
#   ORDER BY "examples_book"."id" ASC
#   LIMIT 1

# Result:
#   Lower      -> the hobbit
#   Upper      -> THE HOBBIT
#   Length     -> 10
#   Substr 1,3 -> The
#   Left 3     -> The
#   Right 3    -> bit
#   LPad 18,·  -> ········The Hobbit
#   RPad 18,·  -> The Hobbit········
#   Trim       -> 'spaced'
#   LTrim      -> 'x'
#   RTrim      -> 'x'
#   Replace o0 -> The H0bbit
#   Reverse    -> tibboH ehT
#   StrIndex   -> 5
#   Repeat ab3 -> ababab
#   Chr(65)    -> A
#   Ord(title) -> 84  (codepoint of 'T')
#   MD5        -> 8b843987fd0b2cd3840a09f76addbe41
#   SHA256     -> ff8d72d1b5733b33b1f53251…
#   Concat     -> The Hobbit — J. R. R. Tolkien