Posted by Mick White on 10/07/05 18:41

Steve wrote:

>>I have a column `cover` VARCHAR(10) that lists a cover charge in the
>>form of "$x.00", where x is the price of admission. I'd like to convert
>>the column to INT UNSIGNED, so that x(a number) is listed, not "$x.00".
>>Is this possible?
>>Thousands of entries are involved.
>
>
> First create a new column, say `cover_int` as INT UNSIGNED
>
> Then execute this:
>
> UPDATE `mytable`
> SET `cover_int` =
> CONVERT(SUBSTRING(`cover`,2,LENGTH(`cover`)-1),UNSIGNED)
>
> Confirm that the new column looks correct, then drop (or rename) the
> old column and rename the new one to `cover`.

That did the trick, Steve. Thanks.
Mick

[Back to original message]