String functions

Concat

class piccolo.query.functions.string.Concat(*args: Column | QueryString | str, alias: str | None = None)

Concatenate multiple values into a single string.

Note

Null values are ignored, so null + '!!!' returns !!!, not null.

Warning

For SQLite, this is only available in version 3.44.0 and above.

Length

class piccolo.query.functions.string.Length(identifier: Column | QueryString | str, alias: str | None = None)

Returns the number of characters in the string.

Lower

class piccolo.query.functions.string.Lower(identifier: Column | QueryString | str, alias: str | None = None)

Converts the string to all lower case, according to the rules of the database’s locale.

Ltrim

class piccolo.query.functions.string.Ltrim(identifier: Column | QueryString | str, alias: str | None = None)

Removes the longest string containing only characters in characters (a space by default) from the start of string.

Replace

class piccolo.query.functions.string.Replace(identifier: Column | QueryString, old: str, new: str, alias: str | None = None)

Replace any instances of old in the string with new.

For example, a really basic slugify implementation:

class Venue(Table):
    name = Varchar()

>>> await Venue.select(Replace(Venue.name, ' ', '-'))
[{'name': 'Amazing-Venue'}]

Reverse

class piccolo.query.functions.string.Reverse(identifier: Column | QueryString | str, alias: str | None = None)

Return reversed string.

Not supported in SQLite.

Rtrim

class piccolo.query.functions.string.Rtrim(identifier: Column | QueryString | str, alias: str | None = None)

Removes the longest string containing only characters in characters (a space by default) from the end of string.

Upper

class piccolo.query.functions.string.Upper(identifier: Column | QueryString | str, alias: str | None = None)

Converts the string to all upper case, according to the rules of the database’s locale.