PostgreSQL Order By

I have been having a problem when PostgreSQL was ordering some data via product name, the product names are as follows:

1. ProductXYZ
2. ProductHJF

...... and so on till

10. Product GHT

The client wanted the page to order them by the number, this worked but alphabetically 10 comes before 1. Due to the data field being varchar PostgreSQL ordered it alphabetically. Now our solution was to either modify the database and our pages that we had coded so far (quite a big task) or develop an SQL query that would rank it numerically. We decided there has to be a way to do this using SQL and we figured it out. If you have the same problem at any time use this.

select * from products order by substring(product_name from 1 for position('.' in product_name)-1)::text::integer

What this does is separate the number from the string, then type casts it as an integer and PostgreSQL sorts it perfectly.

Thanks for the tip Andreas, I'm sure I will run into that problem sooner or later.

very cool method! thanks!