CODEWITHSUNDEEP
mysqldatabase
frgtv10
frgtv10

Reputation: 5460

SQL using MIN with CHAR content in SELECT()

I have the following data (field varchar(55)):

123,00
212,11
1.212,00

With

SELECT MIN(FIELD) FROM XYZ

I get "1.212,00". This is wrong for me, but not wrong for MySQL. I need the following: 123,00.

How can I do this without changing the field type?

TIA Matt

Upvotes: 0

Views: 238

Answers (2)

JonH
JonH

Reputation: 33173

Use Cast or Convert:

The CONVERT() and CAST() functions take a value of one type and produce a value of another type.

SELECT MIN(CAST(Field AS DECIMAL(10,2))) FROM XYZ

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

Upvotes: 0

dani herrera
dani herrera

Reputation: 51715

You should cast your char value to numeric value.

select min(
              CAST( replace(
                      replace ( FIELD, '.', '' ),
                      ',', '.'
                    )
                   AS DECIMAL(5,2))
           )  ...

Upvotes: 1

Related Questions