Sort data from textbox as number?

Question

PHP

Hello!
There is a table with fields that contain mixed data (numbers with letters).
When getting data from this table, you need to sort them as numbers.
I used to work in conjunction with MySQL, but now I needed to switch to Postgress
used a query

select data from ".$prefix."$table WHERE pre_data = '$pre_data' ORDER BY (data+0)

In MySQL, such a query passed without problems, but postgres gives an error.
How can you achieve what you want in postgres?
As a result, I decided through sorting in the array using php methods

// Получение списка столбцов
foreach ($out2 as $key => $row) {
$volume[$key] = $row['data'];
}
//Сортировка
array_multisort($volume,SORT_NUMERIC, SORT_ASC, $out2);

Answer 1

Extract all the numeric data you want to sort by from the data column into a new numeric type column and sort by it.

Answer 2

SELECT * FROM unnest(array['1','2б','2а','1а','2']) a ORDER BY a ASC;

gives
1
1a
2
2a
2b
on postgres 9.4.1

Answer 3

For oracle, these tricks work - www.techonthenet.com/oracle/questions/sort1.php
For Postgre, I think it should work too.

Answer 4

ORDER BY substring(column_name, '^[0-9]+')::int
Or so that after the number the letters are sorted alphabetically

ORDER BY substring(column_name, '^[0-9]+')::int, substring(column_name, '[^0-9]*$')