VARCHAR FOR NUMBER

Question

I have a column (value) in MYSQL in varchar, and in it I store whole or broken numbers, like this:

1.1
2
3.99
4.2
2.345

In the query I need to know when a number is (greater or equal) and (less or equal) to these numbers stored in the bank, I did so:

WHERE valor >= :valor1 AND valor <= :valor2

But it doesn’t work, I believe it occurs because it’s a varchar field, right? How to transform?

Answer 1

Well, this is described in Cast Functions and Operators

The type for the result can be one of the following values:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TEAM
• UNSIGNED [INTEGER]

Therefore, you must use:

SELECT CAST(PRECO AS DECIMAL(5,2)) FROM PRODUTOS

Another example:

SELECT DESCRICAO,PRECO FROM PRODUTOS WHERE CAST(PRECO AS DECIMAL(5,2)) BETWEEN 10 AND 50.5 

I hope I’ve helped!

Source of study

Answer 2

Regarding the integrity of the data, it would be more interesting to change the type of the column, to decimal, if you have difficulty doing so, ask a question.

Now if you can’t change the column type, we’ll have to use the function cast, of which changes the data type of the column.

Let’s just have the table, which already has the data of your question:

CREATE TABLE IF NOT EXISTS tabela (
  coluna varchar(200) NOT NULL
) DEFAULT CHARSET=utf8;

Doing the consultation:

select coluna
  from (SELECT CAST(t.coluna AS DECIMAL(5, 2)) coluna FROM tabela t) a
 where a.coluna >= 2
   AND a.coluna <= 4

We’d be converting the column of VARCHAR, for DECIMAL(5,2). I put in a sub-colony so I wouldn’t be doing the CAST 2x, but I recommend using the between.

Following example: http://sqlfiddle.com/#! 9/d07d15/1