¿Ordenar la columna de cadena que contiene numbers en SQL?

Estoy tratando de orderar la columna de cadena (que contiene numbers ).

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

Verás que el algorithm de sorting natural de Mysql está colocando a 12 después de a 1 (lo cual está bien para la mayoría de las aplicaciones ), pero tengo necesidades únicas, por lo que quiero que el resultado se ordere de esta manera.

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

¿Es posible con solo SQL , o tengo que manipular el set de resultados a nivel de aplicación?

Suponiendo que siempre es WORD_space_NUMBER, esto debería funcionar:

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

Usa POSITION para encontrar el espacio, SUBSTRING para agarrar el número que está detrás de él y CAST para que sea un valor comparable.

Si hay un patrón diferente para la columna, házmelo saber y trataré de idear una mejor solución.


EDITAR Probado para trabajar:

 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) 

ignorar mis nombres de tabla / columna cojos, pero me da el resultado correcto. También fue un poco más allá y agregó una sorting doble para separar el prefijo de letras con numérico.

Editar SUBSTRING_INDEX lo hará un poco más legible.

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

Eche un vistazo a las funciones MySQL CAST / Convert .

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

Editar: leo:

Estoy tratando de orderar la columna de cadena (que contiene numbers).

… pero solo eché un vistazo al set de resultados. ¿Es el a realmente también parte de los contenidos? Si es así, puede usar funciones como MID para extraer solo el valor numérico y lanzarlo.

Pero si todas las filas contienen solo a sin variación, también podrías omitirla …

Puedes intentar esto:

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

Otra opción podría ser rellenar la cadena con espacios a la izquierda del número (es decir, agregar espacios entre la palabra y el número) y usar la cadena resultante para orderar, comething de la siguiente manera:

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

Se supone que la cadena es del patrón 'palabra seguido de espacio (s) seguido de número', y se supone que el número es no negativo (o se orderaría incorrectamente). El codificado 20 se elige arbitrariamente y se supone que es la longitud máxima posible de la parte numérica de la cadena.

Aquí encontré otra solución con la siguiente consulta usando Convert

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