I have a database which has a table containing articles that appear in a magazine. Each article appears on one of more pages of the magazine. In the database page numbers are stored in a character varying (VARCHAR) field with values such as 1, 2, 3, 4, 5, etc. But also with values such as OBC (outside back cover), IFC (inside front cover).
I need to pull the articles out of the database order by page number but am having trouble achieving this with standard SQL and postgres. I think I need a custom written function/stored procedure to sort the results for me.
My ideal sort would be need to be specified, but at the very least it should sort like this:
This is a numeric sort i suppose but on a varchar field with named pages also.
Can anyone help?