Convert to float in sql query

[amacara1]

I am using Version field of EA elements as float numbers. Is there a way to convert the field during an sql query so that I can compare like Version < 1.2 ?
Thank you!

[BobM]

WHERE
(LEFT(VERSION,1) < 1 OR
LEFT(VERSION,1) = 1 AND RIGHT(VERSION,1) < 1)

It will automatically convert to numeric

[Geert Bellekens]

There are also conversion functions to a decimal.
Their syntax depends on the database you are using.

Geert

[amacara1]

WHERE
(LEFT(VERSION,1) < 1 OR
LEFT(VERSION,1) = 1 AND RIGHT(VERSION,1) < 1)

It will automatically convert to numeric

This does help, but I will sooner-rather-than-later reach version 10.10. Then what do I do?

[amacara1]

There are also conversion functions to a decimal.
Their syntax depends on the database you are using.

Geert

I was hoping there is a thing similar to #thing# We are using jet 4 (eapx) and Mysql.

[amacara1]

val() seems to work at my side.

[BobM]

WHERE
(LEFT(VERSION,1) < 1 OR
LEFT(VERSION,1) = 1 AND RIGHT(VERSION,1) < 1)

It will automatically convert to numeric

This does help, but I will sooner-rather-than-later reach version 10.10. Then what do I do?

My apologies I was lazy
REVERSE is not that performant, there might be better solutions but below is a five second tought fix for every single above 9 number

Code: [Select]

WHERE
(LEFT(VERSION,CHARINDEX('.',VERSION)-1) < 1 OR
LEFT(VERSION,CHARINDEX('.',VERSION)-1) = 1 AND RIGHT(VERSION,CHARINDEX('.',REVERSE(VERSION))-1) < 1)

[amacara1]

Thank you, BobM for your time. Pittyfully, CHARINDEX did not work on my jet 4
But, I solved my need with val(), which is ok for now.

[Paolo F Cantoni]

Thank you, BobM for your time. Pittyfully, CHARINDEX did not work on my jet 4
But, I solved my need with val(), which is ok for now.

For MS Access you need INSTR()  (The CHARINDEX equivalent)'

HTH,
Paolo