Django Modals, remove non numeric characters from column, CAST as integer then order by

user_5429320 0
Viewed 367

I have database of cards that has a Number field which represents the number of the card in the set. This field is a CharField as some of the cards have their number followed by a symbol to represent different versions of the same card. I would like to order my data using this field however it orders weird due to the field being a string. For example; 1, 10, 100, 101, 102...

code:

def cards_page(request):
    card_list = Card.objects.exclude(side='b').extra(select={'int': 'CAST(number AS INTEGER)'}).order_by('int')

The code above falls over due to the symbols in this field. I read around that using the below regex code snippet will remove the non numeric characters but cannot seem to get it to work in the select.

"REGEXP_REPLACE(number, '[^0-9.]', '')"

Is there a way to remove the non numeric character before casting to an integer and ordering by that value?

python-3.x postgresql django python
1 Answers

You can use the regexp_match postgres function to extract the first group of digits in the column, and then cast it to an integer.

This is probably better than using the regexp_replace function, because you'd only want the first continuous string of numbers, rather than all of the numbers concatenated.

SQL_EXPRESSION = """
  CAST(
    (REGEXP_MATCH(number, '\d+'))[1] as INTEGER
  )
"""

cards = Card.objects.exclude(side='b').extra(
    select={'number_value': SQL_EXPRESSION}
).order_by('number_value')

Note: If you're using a PostgreSQL version before v10, you'll need to use something like CAST((SELECT REGEXP_MATCHES(number, '\d+')) as INTEGER). It's described in the docs inside of a green "Tip" box.

In django, the query above is more reusable and better written as a queryset function:

# This is just an example, I haven't tested it
from django.db.models import F, Func, IntegerField

class ExtractInteger(Func):
    """ Returns the first int value from the string. Note that this
    requires the string to have an integer value inside.
    """
    function = 'REGEXP_MATCH'
    template = "CAST( (%(function)s(%(expression)s, '\d+'))[1] as INTEGER )"
    output_field = IntegerField()

cards = Card.objects.annotate(
    number_value=ExtractInteger(F('name'))
).order_by('number_value')
user_283600
Related