将字符串转换为整数

Question

我正在从一个包含Varchar原始数据的表中导入数据，我需要将一个Varchar列导入到一个字符串类型的列中。我尝试使用<column_name>::integer以及to_number(<column_name>,'9999999')，但是我遇到了错误，因为有一些空字段，我需要将它们作为空或null值导入到新表中。

- Vijay DJ

---

请提供错误信息，这将有助于解决问题。 - Frank Heikens

如果出现以下错误： Query failed: ERROR: invalid input syntax for integer: ""，请使用intval()函数来解决。 - Abel Callejo

Answer 1

猜测：如果您的值为空字符串，您可以使用NULLIF将其替换为NULL：

SELECT
    NULLIF(your_value, '')::int

- Frank Heikens

Answer 2

你甚至可以进一步限制这个合并字段，例如：

SELECT CAST(coalesce(<column>, '0') AS integer) as new_field
from <table>
where CAST(coalesce(<column>, '0') AS integer) >= 10; 

- tatty

---

为什么coalesce需要转换类型？ - Jeremy Meek

Coalesce本身不需要转换。它只确保返回字符串“0”而不是NULL。需要转换为整数的是结果值（它是一个字符串），根据原始问题。 - Nic Nilov

Answer 3

如果你需要将空列视为NULL，可以尝试这样做：

SELECT CAST(nullif(<column>, '') AS integer);

另一方面，如果您确实有需要避免的NULL值，请尝试：

SELECT CAST(coalesce(<column>, '0') AS integer);

我同意，错误信息会帮助很多。

- vyegorov

Answer 4

我成功地避免了由于NULL、特殊字符或空字符串而导致的错误，唯一的方法就是这样做：

SELECT REGEXP_REPLACE(COALESCE(<column>::character varying, '0'), '[^0-9]*' ,'0')::integer FROM table

- Jade Hamel

---

对我来说（9.6.2），这是唯一有效的方法，其他答案都失败了。 - Jasper de Vries

你能不能加上一个 WHERE <column> != NULL 呢？ - Matthieu

Mathieu：在添加WHERE子句后，将会排除具有NULL值的行，也许您希望将NULL列转换为0。 - Jade Hamel

Answer 5

我无法在Lukas的帖子中评论（声望太低了吗？我很新），

在我的PG设置中， to_number(NULL) 不起作用，所以我的解决方法是：

SELECT CASE WHEN column = NULL THEN NULL ELSE column :: Integer END
FROM table

- niko

---

这个方法应该是有效的，但它应该是比较简洁的 NULLIF() 方法的等价物。标准实际上将 NULLIF 定义为 CASE 谓词的一种形式。 - kgrittn

Answer 6

如果值包含非数字字符，您可以按以下方式将该值转换为整数：

SELECT CASE WHEN <column>~E'^\\d+$' THEN CAST (<column> AS INTEGER) ELSE 0 END FROM table;

CASE运算符检查<column>，如果匹配整数模式，则将rate转换为整数，否则返回0。

- Igor Ostrovsky

Answer 7

常见问题

简单地将任何字符串强制转换为整数，例如：

SELECT ''::integer

经常导致著名错误：

Query failed: ERROR: invalid input syntax for integer: ""

问题

PostgreSQL没有预定义的函数可以将任何字符串安全地转换为整数。

解决方案

创建一个用户定义的函数，灵感来自于PHP的intval()函数。

CREATE FUNCTION intval(character varying) RETURNS integer AS $$

SELECT
CASE
    WHEN length(btrim(regexp_replace($1, '[^0-9]', '','g')))>0 THEN btrim(regexp_replace($1, '[^0-9]', '','g'))::integer
    ELSE 0
END AS intval;

$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

使用方法

/* Example 1 */
SELECT intval('9000');
-- output: 9000

/* Example 2 */
SELECT intval('9gag');
-- output: 9

/* Example 3 */
SELECT intval('the quick brown fox jumps over the lazy dog');
-- output: 0

- Abel Callejo

---

对于非常大的数字会失败。如果你也捕获异常，它将完美地工作。 - jaisonDavis

@jaisonDavis 这个话题是关于整数的，所以预计这个解决方案对于非常大的数字会失败。您可能需要考虑创建一个类似于此解决方案的函数，例如：bigintval()，并且对于该函数，将任何说integer的地方替换为bigint。失败可能只是由于数据类型限制造成的。 - Abel Callejo

Answer 8

您可以使用这个查询。

SUM(NULLIF(conversion_units, '')::numeric)

- Abdul Quadir

Answer 9

对我来说，完美的解决方案是使用nullif和regexp_replace

SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;

上述解决方案考虑了以下边缘情况。

1. 字符串和数字：只有regexp_replace函数能够完美地转换为整数。

SELECT NULLIF(REGEXP_REPLACE('string and 12345', '[^0-9]', '', 'g'), '')::bigint;

仅限字符串：`regexp_replace` 将非字符串字符转换为空字符串；因此无法直接转换为整数，所以请使用 `nullif` 将其转换为 null。

SELECT NULLIF(REGEXP_REPLACE('only string', '[^0-9]', '', 'g'), '')::bigint;

整数范围：将字符串转换为整数可能会导致“类型整数超出范围”的错误。因此，请使用bigint代替。

SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;

- Bedram Tamang

Answer 10

如果你的列有小数点

select NULLIF('105.0', '')::decimal

- Omari Victor Omosa