postgresql 将字符串类型转换为整数 - Postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10518258/
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
typecast string to integer - Postgres
提问by Vijay DJ
I am importing data from a table which has raw feeds in Varchar, I need to import a column in varchar into a string column. I tried using the <column_name>::integeras well as to_number(<column_name>,'9999999')but I am getting errors, as there are a few empty fields, I need to retrieve them as empty or null into the new table.
我正在从一个在 Varchar 中有原始提要的表中导入数据，我需要将 varchar 中的一列导入到一个字符串列中。我尝试使用<column_name>::integer以及to_number(<column_name>,'9999999')但我收到错误，因为有一些空字段，我需要将它们作为空或空检索到新表中。
Kindly let me know if there is a function for the same.
请让我知道是否有相同的功能。
回答by Frank Heikens
Wild guess: If your value is an empty string, you can use NULLIF to replace it for a NULL:
疯狂猜测：如果您的值是一个空字符串，您可以使用 NULLIF 将其替换为 NULL：
SELECT
NULLIF(your_value, '')::int
回答by tatty
You can even go one further and restrict on this coalesced field such as, for example:-
您甚至可以更进一步并限制此合并字段，例如：-
SELECT CAST(coalesce(<column>, '0') AS integer) as new_field
from <table>
where CAST(coalesce(<column>, '0') AS integer) >= 10;
回答by vyegorov
If you need to treat empty columns as NULLs, try this:
如果您需要将空列视为NULLs，请尝试以下操作：
SELECT CAST(nullif(<column>, '') AS integer);
On the other hand, if you do have NULLvalues that you need to avoid, try:
另一方面，如果您确实有NULL需要避免的值，请尝试：
SELECT CAST(coalesce(<column>, '0') AS integer);
I do agree, error message would help a lot.
我同意，错误消息会很有帮助。
回答by Charles Hamel
The only way I succeed to not having an error because of NULL, or special characters or empty string is by doing this:
我成功避免因 NULL、特殊字符或空字符串而出错的唯一方法是执行以下操作：
SELECT REGEXP_REPLACE(COALESCE(<column>::character varying, '0'), '[^0-9]*' ,'0')::integer FROM table
回答by niko
I'm not able to comment (too little reputation? I'm pretty new) on Lukas' post.
我无法对 Lukas 的帖子发表评论（声誉太低？我很新）。
On my PG setup to_number(NULL)does not work, so my solution would be:
在我的 PG 设置to_number(NULL)上不起作用，所以我的解决方案是：
SELECT CASE WHEN column = NULL THEN NULL ELSE column :: Integer END
FROM table
回答by Igor Ostrovsky
If the value contains non-numeric characters, you can convert the value to an integer as follows:
如果值包含非数字字符，您可以将值转换为整数，如下所示：
SELECT CASE WHEN <column>~E'^\d+$' THEN CAST (<column> AS INTEGER) ELSE 0 END FROM table;
The CASE operator checks the < column>, if it matches the integer pattern, it converts the rate into an integer, otherwise it returns 0
CASE 运算符检查<列>，如果匹配整数模式，则将比率转换为整数，否则返回0
回答by Abdul Quadir
you can use this query
你可以使用这个查询
SUM(NULLIF(conversion_units, '')::numeric)
回答by Abel Callejo
Common issue
常见问题
Naively type casting any string into an integer like so
像这样天真地将任何字符串转换为整数
SELECT ''::integer
Often results to the famous error:
经常导致著名的错误：
Query failed: ERROR: invalid input syntax for integer: ""
Problem
问题
PostgreSQL has no pre-defined function for safelytype casting any string into an integer.
PostgreSQL 没有用于安全地将任何字符串转换为整数的预定义函数。
Solution
解决方案
Create a user-defined function inspired by PHP's intval()function.
创建一个受 PHP 的intval()函数启发的用户定义函数。
CREATE FUNCTION intval(character varying) RETURNS integer AS $$
SELECT
CASE
WHEN length(btrim(regexp_replace(, '[^0-9]', '','g')))>0 THEN btrim(regexp_replace(, '[^0-9]', '','g'))::integer
ELSE 0
END AS intval;
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
Usage
用法
/* 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
