SQL 如何将字符串转换为整数并在使用 PostgreSQL 进行转换时出现错误的情况下为 0？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2082686/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
提问by silviot
In PostgreSQL I have a table with a varchar column. The data is supposed to be integers and I need it in integer type in a query. Some values are empty strings. The following:
在 PostgreSQL 中，我有一个带有 varchar 列的表。数据应该是整数，我在查询中需要整数类型。有些值是空字符串。下列：
SELECT myfield::integer FROM mytable
yields ERROR: invalid input syntax for integer: ""
产量 ERROR: invalid input syntax for integer: ""
How can I query a cast and have 0 in case of error during the cast in postgres?
如果在 postgres 中的转换过程中出现错误，我如何查询转换并获得 0？
回答by Anthony Briggs
I was just wrestling with a similar problem myself, but didn't want the overhead of a function. I came up with the following query:
我自己只是在与类似的问题搏斗，但不想要函数的开销。我想出了以下查询：
SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\d+$';
Postgres shortcuts its conditionals, so you shouldn't get any non-integers hitting your ::integer cast. It also handles NULL values (they won't match the regexp).
Postgres 快捷方式它的条件，所以你不应该让任何非整数击中你的 ::integer 演员表。它还处理 NULL 值（它们与正则表达式不匹配）。
If you want zeros instead of not selecting, then a CASE statement should work:
如果您想要零而不是不选择，那么 CASE 语句应该起作用：
SELECT CASE WHEN myfield~E'^\d+$' THEN myfield::integer ELSE 0 END FROM mytable;
回答by Matthew Wood
You could also create your own conversion function, inside which you canuse exception blocks:
您还可以创建自己的转换函数，您可以在其中使用异常块：
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;
Testing:
测试：
=# 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)
回答by ghbarratt
I had the same sort of need and found this to work well for me (postgres 8.4):
我有同样的需求，发现这对我很有效（postgres 8.4）：
CAST((COALESCE(myfield,'0')) AS INTEGER)
Some test cases to demonstrate:
一些测试用例来演示：
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"
If you need to handle the possibility of the field having non-numeric text (such as "100bad") you can use regexp_replace to strip non-numeric characters before the cast.
如果您需要处理字段具有非数字文本（例如“100bad”）的可能性，您可以使用 regexp_replace 在强制转换之前去除非数字字符。
CAST(REGEXP_REPLACE(COALESCE(myfield,'0'), '[^0-9]+', '', 'g') AS INTEGER)
Then text/varchar values like "b3ad5" will also give numbers
然后像“b3ad5”这样的 text/varchar 值也会给出数字
db=> select CAST(REGEXP_REPLACE(COALESCE('b3ad5','0'), '[^0-9]+', '', 'g') AS INTEGER);
regexp_replace
----------------
35
(1 row)
To address Chris Cogdon's concern with the solution not giving 0 for all cases, including a case such as "bad" (no digit characters at all), I made this adjusted statement:
为了解决 Chris Cogdon 对解决方案不为所有情况提供 0 的担忧，包括诸如“坏”（根本没有数字字符）之类的情况，我做了以下调整后的声明：
CAST((COALESCE(NULLIF(REGEXP_REPLACE(myfield, '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
It works similar to the simpler solutions, except will give 0 when the value to convert is non-digit characters only, such as "bad":
它的工作原理类似于更简单的解决方案，除了当要转换的值仅为非数字字符时会给出 0，例如“坏”：
db=> select CAST((COALESCE(NULLIF(REGEXP_REPLACE('no longer bad!', '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
coalesce
----------
0
(1 row)
回答by Matt
This might be somewhat of a hack, but it got the job done in our case:
这可能有点骇人听闻，但在我们的案例中它完成了工作：
(0 || myfield)::integer
Explanation (Tested on Postgres 8.4):
说明（在 Postgres 8.4 上测试）：
The above mentioned expression yields NULLfor NULL-values in myfieldand 0for empty strings (This exact behaviour may or may not fit your use case).
上面提到的表达式为空字符串中的NULLNULL 值myfield和0空字符串产生（这种确切的行为可能适合也可能不适合您的用例）。
SELECT id, (0 || values)::integer from test_table ORDER BY id
Test data:
测试数据：
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');
The query will yield the following result:
查询将产生以下结果：
---------------------
|1|null |NULL|
|2|empty string|0 |
|3|one |1 |
---------------------
Whereas select only values::integerwill result in an error message.
而 select onlyvalues::integer将导致错误消息。
Hope this helps.
希望这可以帮助。
回答by Jan Han?i?
SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable
SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable
I haven't ever worked with PostgreSQL but I checked the manualfor the correct syntax of IF statements in SELECT queries.
我从未使用过 PostgreSQL，但我查看了手册以了解 SELECT 查询中 IF 语句的正确语法。
回答by Erwin Brandstetter
@Matthew's answeris good. But it can be simpler and faster. And the question asks to convert empty strings ('') to 0, but not other "invalid input syntax" or "out of range" input:
@Matthew 的回答很好。但它可以更简单、更快。问题要求将空字符串 ( '')转换为0，而不是其他“无效输入语法”或“超出范围”输入：
CREATE OR REPLACE FUNCTION convert_to_int(text)
RETURNS int AS
$func$
BEGIN
IF = '' THEN -- special case for empty string like requested
RETURN 0;
ELSE
RETURN ::int;
END IF;
EXCEPTION WHEN OTHERS THEN
RETURN NULL; -- NULL for other invalid input
END
$func$ LANGUAGE plpgsql IMMUTABLE;
This returns 0for an empty string and NULLfor any other invalid input.
It can easily be adapted for anydata type conversion.
这将返回0空字符串和NULL任何其他无效输入。
它可以很容易地适用于任何数据类型转换。
Entering an exception block is substantially more expensive. If empty strings are commonit makes sense to catch that case before raising an exception.
If empty strings are very rare, it pays to move the test to the exception clause.
进入异常块的成本要高得多。如果空字符串很常见，那么在引发异常之前捕获这种情况是有意义的。
如果空字符串非常罕见，则将测试移至异常子句是值得的。
回答by deprecated
SUBSTRING may help for some cases, you can limit the size of the int.
SUBSTRING 在某些情况下可能会有所帮助，您可以限制 int 的大小。
SELECT CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer);
回答by Oleg Mikhailov
CREATE OR REPLACE FUNCTION parse_int(s TEXT) RETURNS INT AS $$
BEGIN
RETURN regexp_replace(('0' || s), '[^\d]', '', 'g')::INT;
END;
$$ LANGUAGE plpgsql;
This function will always return 0if there are no digits in the input string.
0如果输入字符串中没有数字，此函数将始终返回。
SELECT parse_int('test12_3test');
SELECT parse_int('test12_3test');
will return 123
将返回 123
回答by Ashish Rana
I found the following code easy and working. Original answer is here https://www.postgresql.org/message-id/[email protected]
我发现以下代码简单且有效。原始答案在这里https://www.postgresql.org/message-id/[email protected]
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)
hope it helps
希望能帮助到你
回答by Bandi-T
If the data is supposed to be integers, and you only need those values as integers, why don't you go the whole mile and convert the column into an integer column?
如果数据应该是整数，而您只需要这些值作为整数，为什么不全力以赴将列转换为整数列？
Then you could do this conversion of illegal values into zeroes just once, at the point of the system where the data is inserted into the table.
然后，您可以在将数据插入表的系统点将非法值转换为零一次。
With the above conversion you are forcing Postgres to convert those values again and again for each single row in each query for that table - this can seriously degrade performance if you do a lot of queries against this column in this table.
通过上述转换，您将强制 Postgres 为该表的每个查询中的每一行一次又一次地转换这些值 - 如果您对该表中的此列进行大量查询，这会严重降低性能。
