在MySQL中将文本列转换为整数 [英] Converting text column to integer in MySQL

问题描述

我正在尝试创建一个清理表，以整数替换varchar(50)字段.原始字段偶尔会有一些文本值，我想将其转换为0或null值. 我可以在select语句中做到这一点，但是在尝试创建表时出现错误.

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.

推荐答案

我将为其使用case语句，并切换到使用CAST而不是convert，这样您将获得失败而不是隐式转换.

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;

我没有方便地测试该实例的mysql实例，因此希望我不会遇到任何语法错误.

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

这篇关于在MySQL中将文本列转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！