Sort mysql query by numbers present in varchar column

Asked

Viewed 117 times

1

I have a table with a varchar column and I want to perform a SELECT and sort the result by the numbers in that column.

The table:

name: coins columns: id, name, price, catalog

The catalogue column consists of a VARHCAR(50), for example, having the following values: currency 1 - 'CJ 0039'; Currency 2 - 'CJ 0025'; currency 3 - 'CJ 0131.2'; Currency 4 - 'CJ 0206';

I want to order the result like this: currency 2; currency 1; currency 3; coin 4;

I’ve tried the following select:

SELECT 
   id, 
   catalogo, 
preco FROM moedas 
order by ABS(CAST(catalogo AS UNSIGNED)) asc
mysql

  • You could show the creation script of your table and select that you have tried?

  • SELECT id, catalogue, price FROM coins order by ABS(CAST(catalogue AS UNSIGNED)) asc

  • If you want to order only by numerical part ?

  • This, by the numeric part present in the column catalog

2 answers

2


I created the table, entered the data and everything went well here.

Follows code:

SELECT
  id,
  nome,
  preco,
  catalogo
FROM moedas
  ORDER BY catalogo ASC

Output from my mysql:

2   |  moeda 2  |  45,66  | CJ 0025
1   |  moeda 1  |  99,55  | CJ 0039
3   |  moeda 3  |  44,55  | CJ 0131.2
4   |  moeda 4  |  55,44  | CJ 0206

  • Simpler than imagined. Thank you.

-1

If all lines are in the same pattern as your example, and assuming that currency x is not part of the field content, try:

SELECT * FROM sua_tabela 
ORDER BY (CASE catalogo WHEN 'CJ 0025' THEN 1 
                        WHEN 'CJ 0039' THEN 2
                        WHEN 'CJ 0131.2' THEN 3
                        WHEN 'CJ 0206' THEN 4) ASC;

If you want to consider only existing numbers within the string, eliminating any alphabetic character, use:

ORDER BY CAST(REGEXP_REPLACE(catalogo, '[[:alpha:]]', '') AS NUMERIC) ASC;

Obviously assuming that your example has only been chosen poorly, since according to it it would be enough to order by the field catalog, which should not actually occur for you to post such a question.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.