Hello,

 

I have a question for the Operation Cast and CONVERT.

 

I have the following table and wants to run this query.

 

select * from value v2 WHERE v2.xpath_id = 378 AND v2.value BETWEEN 1000 AND 2000 LIMIT 10;

 

This provides me with the wrong result because v2.value is a textfield.

Now I wanted to with the following query to fix this problem, but I get an error. Is it at all possible to the cast or convert?

 

select *  from value v2 WHERE v2.xpath_id = 378 AND CAST(CAST(v2.value AS float)AS integer) > 1000 LIMIT 10;

conversion of string 'RNAV' to type dbl failed.

 

But this one Works

 

select CAST(CAST(v2.value AS float)AS integer) from value v2 WHERE v2.xpath_id = 378 LIMIT 10;

 

The problem is, “value” must be a text field, “value” can take all possible values.

 

CREATE TABLE value (

  id int NOT NULL AUTO_INCREMENT,

  element_id int NOT NULL,

  xpath_id int NOT NULL,

  value text,

  PRIMARY KEY (id)

);

 

Thanks 4 Help

 

Greetings Dennis