如何将字符串转换为整数，并且在 PostgreSQL 转换中出现错误时为0？

SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable

我从来没有使用过 PostgreSQL，但是我检查了 手动操作以获得 SELECT 查询中 IF 语句的正确语法。

如果数据应该是整数，而你只需要这些值作为整数，为什么你不全力以赴，把列转换成一个整数列？

然后，您可以在系统中将数据插入到表中的位置将非法值转换为零，这样的转换只需一次。

通过上面的转换，您可以迫使 Postgres 一次又一次地为该表的每个查询中的每一行转换这些值——如果您对该表中的这一列进行了大量查询，这可能会严重降低性能。

您还可以创建自己的转换函数，其中 可以使用异常块:

CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS INTEGER AS $$
DECLARE v_int_value INTEGER DEFAULT NULL;
BEGIN
BEGIN
v_int_value := v_input::INTEGER;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Invalid integer value: "%".  Returning NULL.', v_input;
RETURN NULL;
END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql;

测试:

=# select convert_to_integer('1234');
convert_to_integer
--------------------
1234
(1 row)


=# select convert_to_integer('');
NOTICE:  Invalid integer value: "".  Returning NULL.
convert_to_integer
--------------------


(1 row)


=# select convert_to_integer('chicken');
NOTICE:  Invalid integer value: "chicken".  Returning NULL.
convert_to_integer
--------------------


(1 row)

我自己也在努力解决一个类似的问题，但是我不想要函数的开销。我提出了以下问题:

SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';

Postgres 会快捷方式处理它的条件，所以你不应该让任何非整数符合你的: : 整数强制转换。它还处理 NULL 值(它们与 regexp 不匹配)。

如果您想要零而不是不选择，那么 CASE 语句应该可以工作:

SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;

这也应该做这项工作，但这是跨 SQL，而不是 postgres 特定。

select avg(cast(mynumber as numeric)) from my table

(0 || myfield)::integer

说明(在 Postgres 8.4上测试) :

上面提到的表达式对于 myfield中的 NULL 值产生 NULL，对于空字符串产生 0(这种确切的行为可能适合也可能不适合您的用例)。

SELECT id, (0 || values)::integer from test_table ORDER BY id

测试数据:

CREATE TABLE test_table
(
id integer NOT NULL,
description character varying,
"values" character varying,
CONSTRAINT id PRIMARY KEY (id)
)


-- Insert Test Data
INSERT INTO test_table VALUES (1, 'null', NULL);
INSERT INTO test_table VALUES (2, 'empty string', '');
INSERT INTO test_table VALUES (3, 'one', '1');

查询将产生以下结果:

 ---------------------
|1|null        |NULL|
|2|empty string|0   |
|3|one         |1   |
---------------------

而只选择 values::integer将导致错误消息。

我也有同样的需求，并且发现这对我很有用(postgres 8.4) :

CAST((COALESCE(myfield,'0')) AS INTEGER)

要演示的一些测试用例:

db=> select CAST((COALESCE(NULL,'0')) AS INTEGER);
int4
------
0
(1 row)


db=> select CAST((COALESCE('','0')) AS INTEGER);
int4
------
0
(1 row)


db=> select CAST((COALESCE('4','0')) AS INTEGER);
int4
------
4
(1 row)


db=> select CAST((COALESCE('bad','0')) AS INTEGER);
ERROR:  invalid input syntax for integer: "bad"

如果需要处理字段具有非数字文本(如“100bad”)的可能性，可以在强制转换之前使用 regexp _ place 去除非数字字符。

CAST(REGEXP_REPLACE(COALESCE(myfield,'0'), '[^0-9]+', '', 'g') AS INTEGER)

然后 text/varchar 值(如“ b3ad5”)也会给出数字

db=> select CAST(REGEXP_REPLACE(COALESCE('b3ad5','0'), '[^0-9]+', '', 'g') AS INTEGER);
regexp_replace
----------------
35
(1 row)

为了解决 Chris Cogdon 对于不给所有情况(包括像“ bad”(根本没有数字字符)这样的情况)0的解决方案的担忧，我做了下面这个调整语句:

CAST((COALESCE(NULLIF(REGEXP_REPLACE(myfield, '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);

它的工作原理类似于较简单的解决方案，只是当要转换的值只是非数字字符时，它会给出0，比如“ bad”:

db=> select CAST((COALESCE(NULLIF(REGEXP_REPLACE('no longer bad!', '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
coalesce
----------
0
(1 row)

CREATE OR REPLACE FUNCTION parse_int(s TEXT) RETURNS INT AS $$
BEGIN
RETURN regexp_replace(('0' || s), '[^\d]', '', 'g')::INT;
END;
$$ LANGUAGE plpgsql;

如果输入字符串中没有数字，该函数将始终返回 0。

SELECT parse_int('test12_3test');

将返回 123

我发现以下代码简单易用。原答案是这里 https://www.postgresql.org/message-id/371F1510.F86C876B@sferacarta.com

prova=> create table test(t text, i integer);
CREATE


prova=> insert into test values('123',123);
INSERT 64579 1


prova=> select cast(i as text),cast(t as int)from test;
text|int4
----+----
123| 123
(1 row)

希望能有所帮助

@ Matthew 的回答很好。但它可以更简单、更快捷。这个问题要求将空字符串('')转换为 0，而不是其他“无效的输入语法”或“超出范围”的输入:

CREATE OR REPLACE FUNCTION convert_to_int(text)
RETURNS int AS
$func$
BEGIN
IF $1 = '' THEN  -- special case for empty string like requested
RETURN 0;
ELSE
RETURN $1::int;
END IF;


EXCEPTION WHEN OTHERS THEN
RETURN NULL;  -- NULL for other invalid input


END
$func$  LANGUAGE plpgsql IMMUTABLE;

我也有同样的需求，但是这适用于 JPA 2.0和 Hibernate 5.0。2:

SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword

创造奇迹，我觉得它也适用于 LIKE。

SUBSTRING 在某些情况下可能有帮助，您可以限制 int 的大小。

SELECT CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer);

下面的函数执行此操作

• 对于不可浇铸的结果使用默认值(error_result) ，例如 abc或 999999999999999999999999999999999999999999
• 保持 null为 null
• 删除输入中的空格和其他空格
• 将转换为有效 bigints的值与 lower_bound进行比较，例如仅强制执行正值

CREATE OR REPLACE FUNCTION cast_to_bigint(text)
RETURNS BIGINT AS $$
DECLARE big_int_value BIGINT DEFAULT NULL;
DECLARE error_result  BIGINT DEFAULT -1;
DECLARE lower_bound   BIGINT DEFAULT 0;
BEGIN
BEGIN
big_int_value := CASE WHEN $1 IS NOT NULL THEN GREATEST(TRIM($1)::BIGINT, lower_bound) END;
EXCEPTION WHEN OTHERS THEN
big_int_value := error_result;
END;
RETURN big_int_value;
END;

最后，我设法忽略无效的字符，只获得数字转换文本为数字。

SELECT (NULLIF(regexp_replace(split_part(column1, '.', 1), '\D','','g'), '')
|| '.' || COALESCE(NULLIF(regexp_replace(split_part(column1, '.', 2), '\D','','g'),''),'00')) AS result,column1
FROM (VALUES
('ggg'),('3,0 kg'),('15 kg.'),('2x3,25'),('96+109'),('1.10'),('132123')
) strings;