By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytesting (
val varchar(255));
insert into mytesting values
("3"),("8"),("56"),("70"),("90"),("AK"),("CN"),("PP"),
("PQ"),("W3"),("0.5"),("0.6"),("0.8"),("040"),("070"),
("1.2"),("1.5"),("1.6"),("100"),("150"),("187"),("2.8"),
("250"),("3.0"),("6.3"),("800"),("8mm");
SELECT val,
val REGEXP '^[a-zA-Z]',
CAST(val AS DECIMAL(14,4))
FROM mytesting
ORDER BY CASE WHEN val REGEXP '^[a-zA-Z]' >= 1 THEN 1 ELSE 0 END ASC,
CAST(val AS DECIMAL(5,2)) ASC;
| val | val REGEXP '^[a-zA-Z]' | CAST(val AS DECIMAL(14,4)) |
|---|---|---|
| 0.5 | 0 | 0.5000 |
| 0.6 | 0 | 0.6000 |
| 0.8 | 0 | 0.8000 |
| 1.2 | 0 | 1.2000 |
| 1.5 | 0 | 1.5000 |
| 1.6 | 0 | 1.6000 |
| 2.8 | 0 | 2.8000 |
| 3 | 0 | 3.0000 |
| 3.0 | 0 | 3.0000 |
| 6.3 | 0 | 6.3000 |
| 8 | 0 | 8.0000 |
| 8mm | 0 | 8.0000 |
| 040 | 0 | 40.0000 |
| 56 | 0 | 56.0000 |
| 070 | 0 | 70.0000 |
| 70 | 0 | 70.0000 |
| 90 | 0 | 90.0000 |
| 100 | 0 | 100.0000 |
| 150 | 0 | 150.0000 |
| 187 | 0 | 187.0000 |
| 250 | 0 | 250.0000 |
| 800 | 0 | 800.0000 |
| W3 | 1 | 0.0000 |
| PQ | 1 | 0.0000 |
| PP | 1 | 0.0000 |
| CN | 1 | 0.0000 |
| AK | 1 | 0.0000 |