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',''))