Converting text column to integer in MySQL

Question

I'm trying to create a cleaned-up table that replaces a varchar(50) field with an integer. The original field has occasional text values that I'd like to convert to 0 or null values. I can do this in a select statement just fine, but get an error when trying to create my table.

Below is a minimal example that uses strings:

/* This works */
DROP TABLE IF EXISTS mytest;
CREATE TABLE mytest
AS
   SELECT convert("123", unsigned) AS mynum;

/* This works, returning a row of 0 */
SELECT convert("TEST", unsigned) AS mynum;

/* But this fails, with:  Truncated incorrect INTEGER value: 'TEST'*/
DROP TABLE IF EXISTS mytest;
CREATE TABLE mytest
AS
   SELECT convert("TEST", unsigned) AS mynum;`

What is wrong with the above, and is there a better way to accomplish what I want? Thanks for the help.

mellamokb · Accepted Answer · 2012-07-25 16:34:57Z

2

I don't have an explanation for why that error occurs, but I found a workaround using a subquery:

DROP TABLE IF EXISTS mytest;
CREATE TABLE mytest
AS
    SELECT mynum
    FROM (SELECT convert("TEST"), unsigned) AS mynum) t;

Demo: http://www.sqlfiddle.com/#!2/4909a/1

answered Jul 25, 2012 at 16:34

mellamokb

56.8k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raj · Accepted Answer · 2012-08-26 01:18:28Z

2

I'd use a case statement for it and also switch over to using CAST instead of convert so you'll get failures instead of implicit conversions.

CREATE TABLE mytest(myVarchar varchar(10));
INSERT mytest VALUES ('123'),('TEST');

SELECT CASE
    WHEN  myVarchar REGEXP '^[0-9]+$' THEN CAST(myVarchar,unsigned)
    ELSE 0
END As mynum
FROM mytest;

I don't have a mysql instance handy to test this so hopefully I didn't goof any syntax errors.

edited Aug 26, 2012 at 1:18

Raj

23.1k14 gold badges105 silver badges148 bronze badges

answered Jul 25, 2012 at 16:45

user893460

212 bronze badges

1 Comment

JIm Over a year ago

This worked, thanks. It seems like there should be another way, however, that doesn't rely on regexp matching.

dnagirl · Accepted Answer · 2012-07-25 16:38:19Z

1

Rather than convert, you could update the data and then alter the table:

UPDATE mytest SET mynum=mynum + 0;
ALTER TABLE mytest CHANGE COLUMN mynum mynum INT UNSIGNED;

answered Jul 25, 2012 at 16:38

dnagirl

20.5k13 gold badges86 silver badges127 bronze badges

2 Comments

cassi.lup Over a year ago

This does not work. Altering the column reverts all values to 0.

dnagirl Over a year ago

@cassi.lup: If the value in mytest.mynum is a number stored as text (e.g. "23"), then when the column is altered, it will be stored as an integer (e.g. 23). If the value is a string (e.g. "foo"), then altering the datatype to integer will change the value to 0. If the value is a string that begins with a number (e.g. "23foo") then altering the datatype to integer will store the value as 23. In any event, since the OP asked for a method that would maintain any existing integers and translate text values to zero, my answer is correct and does not deserve a downvote.

Hermit · Accepted Answer · 2012-07-25 16:46:38Z

0

It looks like you are implicitly defining the column name by the select statement in the create. This may be assuming the "TEST" string is the datatype. It would help to see the error message you get, but my assumption would be to explicitly define the column as:

CREATE TABLE mytest(mynum int);

Then

Insert into mytest(mynum)
select convert("TEST",unsigned) as mynum;

This is also why the answer with the subquery may work, by the time it gets to the outer query it is implicitly defined as an int.

answered Jul 25, 2012 at 16:46

Hermit

3871 silver badge3 bronze badges

1 Comment

JIm Over a year ago

Thanks, but I had tried this, but it fails also.

Error:7/25/2012 1:23:05 PM 0:00:00.000: Lookup Error - MySQL Database Error: Truncated incorrect INTEGER value: 'TEST' cc1-load.sql:171: Insert into mytest(mynum)

Collectives™ on Stack Overflow

Converting text column to integer in MySQL

4 Answers 4

Comments

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related