slice a query result string field in annotation using Substr and negative values

Question

I am trying to slice the last two characters of a field and annotate it to query result, using the method given in this post. Something like:

Person.objects.all()\
.annotate(
    code = Substr('str', 1, -2),
)

The above code returns an empty string as soon as I use negative values as the third argument. How can I achieve this?

Answer 1

You need to annotate a field for a positive index at first. Then you can use the Substr as follows:

Person.objects.annotate(
    PositiveIndex=ExpressionWrapper(Length('str') - 1, output_field=CharField()),
    code=Substr('str', F('PositiveIndex'))
).values('code')