Text field to integer conversion 1

[stephenwales]

I have a database that has a text field that holds (for the most part) a numeric value.

I need to make this number be 10% of it's size.

The basic SQL is easy:

update table
set val = val/10
where tag like 'StatusField%'

But, since val is of datatype text, the statement is going to fail.

I's sure that there's some form of datatype conversion function that could be used, but I'm not that MySQL literate.

Is there a TextToInt function or something that can be used to convert this text field to an integer for mathematical purposes?

Thanks
Steve

 

[TonyGroves]

With that statement, the field will be converted to a number before being operated on. If it contains only numeric characters, then you're safe. If it contains a mixture of numeric and non-numeric then as far as I know it will use the leading numeric characters. Otherwise, it converts to zero.

 

[stephenwales]

Almost there.

This very accurately worked, right down to the decimal point after thge divide by 10...

Is there a trunc function or something so that I can do pure integer division and drop the decimal point?

Is if val = 9333 and I do val = val/10 I want the answer to be 933, not 933.3

Thanks
Steve

 

[TonyGroves]

You can read about numeric functions in the (F) manual:

http://dev.mysql.com/doc/mysql/en/mathematical-functions.html

 

[stephenwales]

Perfect! Thanks a lot for the help. Have a star!