How to sort on text data type

I have a table like below:

ID  | text_field
----| ----------
 1  | ABC-432
 2  | ABC-1
 3  | ABC-10
 4  | ABC-5

I would like to get a list of the top two results based on the text_field number part

Based on the above data the query output would be:

ID  | text_field
----| ----------
 1  | ABC-432
 3  | ABC-10

Since 432 and 10 are the two highest numbers in this data set.

Answer

You can take advantage of the flexibility of Postgres string function substring(), that supports regular expressions.

SELECT *
FROM t
ORDER BY substring(val, 'd+$')::integer DESC
LIMIT 2

Regexp 'd+$' means : all digits at the end of the string. You need to cast it to an integer so you can perform a numerical sort.

Demo on DB Fiddle :

| val     |
| ------- |
| ABC-432 |
| ABC-10  |