Ordenar cadena correctamente

[totti026]

Hola que tal, tengo un campo "codigo" de tipo varchar que puede contener el siguiente formato XXX-XXX-X ó XXX-XXX-XXX ó XXXX-XXXX-XXX
osea que
el 1er bloque de 3 a 4 digitos, el 2do bloque de 3 a 4 digitos y el 3er bloque puede ser de 1 a 3 digitos.

El problema que tengo es que mi consulta no me la ordena correctamente.
ejemplo: Tengo estas cadenas y me las ordena asi con la consulta

Código MySQL:

SELECT * from codigos ORDER BY codigo ASC
 

200-201-4
200-201-5
200-201-1
200-201-3
200-201-11
300-301-3
300-302-3
300-301-2
300-302-1
300-301-200
1100-1107-1
1200-1234-1
1400-1401-2
1400-1401-1
1400-1401-100

y lo que pretendo es que me ordene la cadena y ordenados los 3 bloques asi:

200-201-1
200-201-3
200-201-4
200-201-5
200-201-11
300-301-2
300-301-3
300-301-200
300-302-1
300-302-3
1100-1107-1
1200-1234-1
1400-1401-1
1400-1401-2
1400-1401-100

[quimfv]

Esa cadea es un string luego se va a ordenar según las reglas de ordenació de un string (es decir igual que se ordena un diccionario, teniendo en cuenta que los números van por delante de las letras).

El ejemplo de datos que has puesto se ordena de dos formas según si eliges ASC o DESC

Código MySQL:

SELECT * FROM codigos ORDER BY codigo ASC
 

1100-1107-1
1200-1234-1
1400-1401-1
1400-1401-100
1400-1401-2
200-201-1
200-201-11
200-201-3
200-201-4
200-201-5
300-301-2
300-301-200
300-301-3
300-302-1
300-302-3

Código MySQL:

SELECT * FROM codigos ORDER BY codigo DESC
 

300-302-3
300-302-1
300-301-3
300-301-200
300-301-2
200-201-5
200-201-4
200-201-3
200-201-11
200-201-1
1400-1401-2
1400-1401-100
1400-1401-1
1200-1234-1
1100-1107-1

No hay forma de ordenarlos de otra manera, sin tocar la estructura de datos o sin trabjar con subcadenas.

Estructura:

Usar tres campos de tipo numérico y

Código MySQL:

SELECT * FROM codigos ORDER BY codigo1,codigo2,codigo3 ASC
 

Subcadenas:

En el maual encontramos el siguiente post, donde ya se nos dice que no existe una funció tipo split o explode para separar listas delimitadas por separadores pero dan alguna posible solución

Posted by Bob Collins on March 17 2006 8:56pm

MySQL does not include a function to split a delimited string. Although separated data would normally be split into separate fields within a relation data, spliting such can be useful either during initial data load/validation or where such data is held in a text field.

The following formula can be used to extract the Nth item in a delimited list, in this case the 3rd item "ccccc" in the example comma separated list.

select replace(substring(substring_index('aaa,bbbb,ccccc' , ',', 3), length(substring_index('aaa,bbbb,ccccc', ',', 3 - 1)) + 1), ',', '') ITEM3

The above formula does not need the first item to be handled as a special case and returns empty strings correctly when the item count is less than the position requested.

Posted by [name withheld] on March 22 2006 8:02pm

This will split an IP address ("a.b.c.d") into 4 respective octets:

SELECT
`ip` ,
SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM log_table

A todo ello tienes que agregar el convertir los tres valores a numericos

CAST(str AS UNSIGNED)

Código MySQL:

SELECT * FROM codigos ORDER BY
CAST(SUBSTRING_INDEX( `codigo` , '-', 1 ) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `codigo` , '-', 2 ),'-',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `codigo` , '-', -2 ),'-',1) AS UNSIGNED);
 

no lo he probado, para estar seguro de con que esta ordenendo haz primero

Código MySQL:

SELECT *,
CAST(SUBSTRING_INDEX( `codigo` , '-', 1 ) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `codigo` , '-', 2 ),'-',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `codigo` , '-', -2 ),'-',1) AS UNSIGNED)
FROM codigos ORDER BY
CAST(SUBSTRING_INDEX( `codigo` , '-', 1 ) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `codigo` , '-', 2 ),'-',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `codigo` , '-', -2 ),'-',1) AS UNSIGNED);
 

es mucho mejor crear los tres campos y trabajar con valores numéricos directamente

[totti026]

quimfv mil gracias, ahora tengo una idea mas clara y bueno ahora a estudiar esa funcion.

Ya lo realice y si me acomoda el 1er y el 2do bloque pero aun falta el tercero..
es cuestion del ORDER BY?

De verdad muchas gracias.

[totti026]

Bueno al parecer ya me quedo...
solo modifique el -2 por 3
y el 1 por el -1.
Pero sinceramente aun no se porque se usan números negativos.

Código MySQL:

SELECT *,
CAST(SUBSTRING_INDEX( `partida_presupuestal` , '-', 1 ) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `partida_presupuestal` , '-', 2 ),'-',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `partida_presupuestal` , '-', 3 ),'-',-1) AS UNSIGNED)
FROM comprobacioneselect WHERE id_proyecto='$id_proyecto' ORDER BY
CAST(SUBSTRING_INDEX( `partida_presupuestal` , '-', 1 ) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `partida_presupuestal` , '-', 2 ),'-',-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( `partida_presupuestal` , '-', -2 ),'-',1) AS UNSIGNED);
 

[gnzsoloyo]

Pero sinceramente aun no se porque se usan números negativos.

Si lees el manual de referencia, verás que el numero negativo indica desde donde se empieza a leer la cadena, si desde la derecha (negativos) o desde la izquierda (positivos). Es decir, cuál es el punto cero de la cadena.
Otros DBMS usan la misma lógica en ciertas funciones.