ordenar campo precio varchar

[ferranWEB]

buenas,

tengo una base de datos y quiero ordenar por precio. tengo el campo definido como varchar en la bd, y tengo valores tal que asi: 750.000, 500.000, 1.000.000....

y claro, cuando quiero ordenar no me hace caso. me podeis aconsejar como hacerlo?

[MYSQL]
... WHERE precio > 500.000 ....

[/MYSQL]

alguna sugerencia?

saludos!

[LozanoCavero]

Debes de convertir:
where cast(precio as Decimal(10,2))>500.00

[gnzsoloyo]

No va a poder convertirlo a DECIMAL, porque ha usado un VARCHAR para poder guardar el valor con separadores de miles, cosa que no se debe hacer jamás.
Debió usar un campo DECIMAL; ahora, para poder ordenar los valores primero tiene que expurgarlos de basura (los separadores), reemplazar la coma por el punto, si la usó, y recién allí podrá convertir el valor para ordenarlo.
Es un error, producto de suponer que los valores numéricos se almacenan como cifras, cuando en realidad se almacenan como binarios.
Te conviene realizar la conversión de la columna a un tipo DECIMAL, para poder tener el valor un formato compatible con todos los sistemas. Eso, antes de preocuparse por ordenar...
Quedaría algo así:

Código MySQL:

SELECT CAST(REPLACE(REPLACE('500.000,23','.', ''),',','.') AS DECIMAL(20,2)) PRECIO
 

Lo que sería muy engorroso, para aplicarlo cada vez que debas modificar los dirty data que tienes en la base.

El problema de representar un valor '500000.23', como '500.000,23', es un asunto que se resuelve en la consulta, o bien en la interfase, y no usando un tipo de campo que no corresponde (y que además termina necesitando más espacio de disco).

[ferranWEB]

buenas y gracias a los 2!!!

estoy a tiempo de modificar el dato, hace poco que empece la base de datos y el campo precio está por rellenar. que me recomendais, un decimal? como haria el select entonces?

y a la hora de insertarlo des de la intranet, se puede insertar tal cual, es decir, 500.000, 760.000, 1.900.000....?

saludos y gracias!

[gnzsoloyo]

El manual de MySQL recomienda usar DECIMAL para los casos de valores con decimales que requieran precisión, como es el caso de valores monetarios.
En ese caso lo que tienes que determinar es la cantidad de dígitos enteros y decimales que usarás, ya que el DECIMAL requiere indicar la longitud del valor mayor (incluyendo el punto o coma decimal) y la cantidad de decimales a usar.
Así, DECIMAL(l, d), indicado como

Código MySQL:

DECIMAL(12,2)
 

significa que el mayor entero posible será 999999999, y el rango máximo será de -999999999.99 a 999999999.99.
Si cuentas los "9", verás que hay sólo 11, mientras que estoy indicando 12 como longitud de campo. Eso es porque un byte se usa para indicar la posición del punto decimal.

Respecto a la representación de los valores en pantalla, eso depende del lenguaje usado. En VB.Net, por ejemplo, eso se puede realizar por medio de objetos Globalization.CultureInfo, dos de cuyas propiedades permiten indicar qué separador decimal y de miles se usarán.

Fíjate en el método indicado en el manual de referencia en tu lenguaje de programación.

[ferranWEB]

buenas,

he modificado el campo decimal tal y como indicabas, precio Decimal(2,0). me ha modificado los valores, y donde tenia por ejemplo 760.000 me ha puesto 760. de que forma ahora modifico ese valor? al ponerle . o , no me modifica. que hago mal?

saludos!

[gnzsoloyo]

No te percataste de que te dije que para hacer la conversión debías primero usar esta forma:

Código MySQL:

CAST(REPLACE(REPLACE(precio,'.', ''),',','.') AS DECIMAL(20,2))
 

porque lo que tienes son datos sucios (dirty data), producto de ingresar un datos en un formato incorrecto.

El punto, en las bases de datos, es el separador de decimales y no de miles, por lo que te está cortando el valor donde se supone que es correcto (de hecho, el punto no existe desde la óptica del binario, sino que es una forma codificación que el DBMS "reconstruye" cuando consultas).
El asunto es que antes de hacer la conversión del campo a DECIMAL, primero tienes que corregir el dato guardado. Esto se hace:

Código MySQL:

UPDATE tabla 
SET precio CAST(REPLACE(REPLACE(precio,'.', ''),',','.') AS DECIMAL(20,2));
 

Y recién allí puedes cambiar el tipo de columna...

[ferranWEB]

gracias gnzsoloyo

he probado a hacer el update de la table y me da el siguiente error:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST(REPLACE(REPLACE(precio,'.', ''),',','.') AS DECIMAL(20,2))

que hago mal?

saludos!

[gnzsoloyo]

Código MySQL:

UPDATE tabla 
SET precio = CAST(REPLACE(REPLACE(precio,'.', ''),',','.') AS DECIMAL(20,2));
 

¿Estaba sin el "="?

[ferranWEB]

me sigue dando error: yo ejecuto:

UPDATE viviendas_esp
SET precio = CAST(REPLACE(REPLACE(precio,'.', ''),',','.') AS DECIMAL(20,2))

y me devuelve

MySQL ha dicho:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECIMAL(20,2))' at line 2

saludos

[gnzsoloyo]

Lo siento, el CAST no iba en este caso...

Fase 1: Normalizar los valores:

Código MySQL:

UPDATE viviendas_esp
SET precio = REPLACE(REPLACE(precio, '.', ''), ',', '.') ;
 

Fase 2: Modificar el tipo de columna.

Código MySQL:

ALTER TABLE viviendas_esp MODIFY COLUMN precio decimal(20,2);
 

[ferranWEB]

lo sientes!! no te disculpes faltaria mas!!!

una cosa solo. ahora en donde tenia insertado en el varchar, 1.100.000 tengo lo siguiente 1100000.00. esa es la forma correcta en la que hay que insertarlo en la bd?

y de que forma tengo que parsearlo en el xp?

saludos!

[gnzsoloyo]

Ese es el formato correcto.
Para darte alguna indicación, necesitaría que me dijeses qué lenguajes estás usando para la aplicación.
Como adelanto, en SQL, si quisieras modificar un valor, la sintaxis quedaría:

Código MySQL:

UPDATE viviendas_esp 
SET precio = 125486.38
WHERE vivienda_id = 1253;
 

Como podrás ver, el tema es que los valores numéricos no van entre apostrofes, y los decimales se representan con el punto.
Todo el resto es responsabilidad de las interfaces gráficas, y es allí donde se resuelve. Pero para darte algún consejo más, necesitaría saber con qué estás trabajando.

[ferranWEB]

buenas gnzsoloyo,

estoy trabajando con php. y claro, ahora con lo que hicimos se muetsra el dato en pantalla tal que asi: precio:750000, y me gustaria formatear ese valor a 750.000 aunque en la bd para poder hacer el orden correctamente este como indicas!

saludos y gracias por tus molestias!

[gnzsoloyo]

En el caso de PHP, hay una clase que maneja eso NumberFormatter.
En esa página vas a encontrar los casos específicos, que en tu tema sería NumberFormatter::formatCurrency
Allí vas a encontrar los ejemplos que necesitas.