Ordinamento della colonna di stringhe contenente numeri in SQL?

Sto cercando di ordinare la colonna di stringhe ( contenente numeri ).

// SELECT `name` FROM `mytable` ORDER BY `name` ASC +----------+ +-- name --+ +----------+ +-- a 1 ---+ +-- a 12 --+ +-- a 2 ---+ +-- a 3 ---+ 

Vedete che l’algoritmo di ordinamento naturale di Mysql sta posizionando a 12 dopo a 1 ( che è ok per la maggior parte delle app ), ma ho esigenze uniche, quindi voglio che il risultato debba essere ordinato in questo modo.

 +----------+ +-- name --+ +----------+ +-- a 1 ---+ +-- a 2 ---+ +-- a 3 ---+ +-- a 12 --+ 

È ansible con solo SQL , o devo manipolare il set di risultati a livello di applicazione?

Partendo dal presupposto che sia sempre WORD_space_NUMBER questo dovrebbe funzionare:

 SELECT * FROM table ORDER BY CAST(SUBSTRING(column,LOCATE(' ',column)+1) AS SIGNED) 

Usa POSITION per trovare lo spazio, SUBSTRING per prendere il numero dopo di esso e CAST per renderlo un valore comparabile.

Se c’è un modello diverso per la colonna, fammelo sapere e cercherò di escogitare una soluzione migliore.


EDIT Provato per funzionare:

 mysql> INSERT INTO t (st) VALUES ('a 1'),('a 12'),('a 6'),('a 11'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t ORDER BY st; +----+------+ | id | st | +----+------+ | 1 | a 1 | | 4 | a 11 | | 2 | a 12 | | 3 | a 6 | +----+------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED); +----+------+ | id | st | +----+------+ | 1 | a 1 | | 3 | a 6 | | 4 | a 11 | | 2 | a 12 | +----+------+ mysql> INSERT INTO t (st) VALUES ('b 1'),('b 12'),('b 6'),('b 11'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED); +----+------+ | id | st | +----+------+ | 1 | a 1 | | 5 | b 1 | | 3 | a 6 | | 7 | b 6 | | 4 | a 11 | | 8 | b 11 | | 2 | a 12 | | 6 | b 12 | +----+------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM t ORDER BY LEFT(st,LOCATE(' ',st)), CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED); +----+------+ | id | st | +----+------+ | 1 | a 1 | | 3 | a 6 | | 4 | a 11 | | 2 | a 12 | | 5 | b 1 | | 7 | b 6 | | 8 | b 11 | | 6 | b 12 | +----+------+ 8 rows in set (0.00 sec) 

ignora i miei nomi di tabelle / colonne lame, ma mi dà il risultato corretto. Inoltre è andato un po ‘oltre e ha aggiunto il doppio tipo per rompere il prefisso di lettere con numerico.

Modifica SUBSTRING_INDEX lo renderà un po ‘più leggibile.

 ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED) 

Dai un’occhiata alle funzioni di MySQL CAST / Convert .

 SELECT name FROM mytable ORDER BY CAST(name AS INTEGER) ASC; 

Modifica: ho letto:

Sto cercando di ordinare la colonna di stringhe (contenente numeri).

… ma ho appena dato un’occhiata al set di risultati. L’a è anche parte dei contenuti? Se è così, puoi usare funzioni come MID per estrarre solo il valore numerico e lanciarlo.

Ma se tutte le righe contengono solo a variazione senza, potresti anche ometterlo …

Puoi provare questo:

 ORDER BY CASE WHEN ISNUMERIC(column) THEN cast(column as int) else ascii(column[1,1]) end 

Un’altra opzione potrebbe essere quella di riempire la stringa con spazi a sinistra del numero (cioè aggiungere spazi tra la parola e il numero) e usare la stringa risultante per l’ordinamento, comething come questo:

 ORDER BY INSERT( column, LOCATE(' ', column), 0, SPACES(20 - LENGTH(column) + LOCATE(' ', column)) ) 

Si presume che la stringa sia del tipo “parola seguita da uno o più spazi seguiti da un numero”, e si presume che il numero sia non negativo (oppure sarebbe ordinato in modo errato). Il 20 hardcoded viene scelto arbitrariamente e si suppone che sia la lunghezza massima ansible della parte numerica della stringa.

Qui ho trovato un’altra soluzione con seguente query utilizzando Converti

 select * from tablename where columnname like '%a%' order by Convert(smallint,Replace(columnname,'a',''))