blocks|key|360570|text|胡乱猜测:如果您的值是一个空字符串，您可以使用NULLIF将其替换为NULL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|360571|SELECT
++++NULLIF(your_value,+'')::int|code-block|syntax|javascript|360572|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Wild guess: If your value is an empty string, you can use NULLIF to replace it for a NULL:

<pre><code>SELECT
 NULLIF(your_value, '')::int
</code></pre>

blocks|key|576518|text|您甚至可以进一步限制此合并字段，例如：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|576519|SELECT+CAST(coalesce(<column>,+'0')+AS+integer)+as+new_field
from+<table>
where+CAST(coalesce(<column>,+'0')+AS+integer)+>=+10;+|code-block|syntax|javascript|576520|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can even go one further and restrict on this coalesced field such as, for example:-

<pre><code>SELECT CAST(coalesce(&lt;column&gt;, '0') AS integer) as new_field
from &lt;table&gt;
where CAST(coalesce(&lt;column&gt;, '0') AS integer) &gt;= 10; 
</code></pre>

blocks|key|360614|text|如果您需要将空列视为NULL，请尝试以下操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|360615|SELECT+CAST(nullif(<column>,+'')+AS+integer);|code-block|syntax|javascript|360616|另一方面，如果您确实有需要避免的NULL值，请尝试：|360617|SELECT+CAST(coalesce(<column>,+'0')+AS+integer);|360618|我确实同意，错误消息会有很大帮助。|360619|entityMap^0|A|4|0|0|G|4|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

If you need to treat empty columns as <code>NULL</code>s, try this:

<pre><code>SELECT CAST(nullif(&lt;column&gt;, '') AS integer);
</code></pre>

On the other hand, if you do have <code>NULL</code> values that you need to avoid, try:

<pre><code>SELECT CAST(coalesce(&lt;column&gt;, '0') AS integer);
</code></pre>

I do agree, error message would help a lot.

blocks|key|576591|text|我成功避免因为NULL、特殊字符或空字符串而出现错误的唯一方法是这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|576592|SELECT+REGEXP_REPLACE(COALESCE(<column>::character+varying,+'0'),+'[%5E0-9]*'+,'0')::integer+FROM+table|code-block|syntax|javascript|576593|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The only way I succeed to not having an error because of NULL, or special characters or empty string is by doing this:

<pre><code>SELECT REGEXP_REPLACE(COALESCE(&lt;column&gt;::character varying, '0'), '[^0-9]*' ,'0')::integer FROM table
</code></pre>

blocks|key|576428|text|我不能评论(太少的声誉？我是个新手)在Lukas的帖子上。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|576429|在我的PG设置上，to_number(NULL)不工作，所以我的解决方案是：|offset|length|style|CODE|576430|SELECT+CASE+WHEN+column+=+NULL+THEN+NULL+ELSE+column+::+Integer+END
FROM+table|code-block|syntax|javascript|576431|entityMap^0|0|9|F|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I'm not able to comment (too little reputation? I'm pretty new) on Lukas' post.

On my PG setup <code>to_number(NULL)</code> does not work, so my solution would be:

<pre><code>SELECT CASE WHEN column = NULL THEN NULL ELSE column :: Integer END
FROM table
</code></pre>

blocks|key|360833|text|如果值包含非数字字符，则可以将值转换为整数，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|360834|SELECT+CASE+WHEN+<column>~E'%5E\\d%2B$'+THEN+CAST+(<column>+AS+INTEGER)+ELSE+0+END+FROM+table;|code-block|syntax|javascript|360835|CASE运算符检查<+column>，如果匹配整数模式，则将速率转换为整数，否则返回0|360836|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If the value contains non-numeric characters, you can convert the value to an integer as follows:

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

The CASE operator checks the &lt; column>, if it matches the integer pattern, it converts the rate into an integer, otherwise it returns 0

blocks|key|576688|text|您可以使用此查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|576689|SUM(NULLIF(conversion_units,+'')::numeric)|code-block|syntax|javascript|576690|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can use this query

<pre><code>SUM(NULLIF(conversion_units, '')::numeric)
</code></pre>

blocks|key|360912|text|如果你的列有小数点|type|unstyled|depth|inlineStyleRanges|entityRanges|data|360913|select+NULLIF('105.0',+'')::decimal|code-block|syntax|javascript|360914|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

And if your column has decimal points
<pre><code>select NULLIF('105.0', '')::decimal
</code></pre>

blocks|key|576891|text|这对我来说很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|576892|select+(left(regexp_replace(coalesce('<column_name>',+'0')+%7C%7C+'',+'[%5E0-9]',+'',+'g'),+8)+%7C%7C+'0')::integer|code-block|syntax|javascript|576893|为了便于查看，请执行以下操作：|576894|select+(
++++left(
++++++++regexp_replace(
++++++++++++--+if+null+then+'0',+and+convert+to+string+for+regexp
++++++++++++coalesce('<column_name>',+'0')+%7C%7C+'',
++++++++++++'[%5E0-9]',
++++++++++++'',
++++++++++++'g'
++++++++),++++++--+remove+everything+except+numbers
++++++++8+++++++--+ensure+::integer+doesn't+overload
++++)+%7C%7C+'0'++++--+ensure+not+empty+string+gets+to+::integer
)::integer|576895|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This works for me:
<pre class="lang-sql prettyprint-override"><code>select (left(regexp_replace(coalesce('&lt;column_name&gt;', '0') || '', '[^0-9]', '', 'g'), 8) || '0')::integer
</code></pre>
For easy view:
<pre class="lang-sql prettyprint-override"><code>select (
 left(
 regexp_replace(
 -- if null then '0', and convert to string for regexp
 coalesce('&lt;column_name&gt;', '0') || '',
 '[^0-9]',
 '',
 'g'
 ), -- remove everything except numbers
 8 -- ensure ::integer doesn't overload
 ) || '0' -- ensure not empty string gets to ::integer
)::integer
</code></pre>

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 <code>&lt;column_name&gt;::integer</code> as well as <code>to_number(&lt;column_name&gt;,'9999999')</code> 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.

Typecast string to integer

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

问将字符串类型转换为整数
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将字符串类型转换为整数EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将字符串类型转换为整数
EN