Typecast string to integer

Question

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>::integer as 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.

If the error is something like `Query failed: ERROR: invalid input syntax for integer: ""` see the solution using the [intval()](https://stackoverflow.com/questions/10518258/typecast-string-to-integer-postgres#60485123) function — Abel Callejo, Oct 17 '20 at 00:29

score 155 · Answer 1 · answered May 09 '12 at 14:43

155

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

SELECT
    NULLIF(your_value, '')::int

answered May 09 '12 at 14:43

Frank Heikens

106,435
24
133
132

score 62 · Answer 2 · answered Dec 01 '12 at 13:38

62

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;

answered Dec 01 '12 at 13:38

tatty

686
6
4

why does coalesce need casting? – Jeremy Meek Mar 12 '22 at 21:40

vyegorov · Answer 3 · 2012-05-09T16:19:25.703

31

If you need to treat empty columns as NULLs, try this:

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

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

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

I do agree, error message would help a lot.

edited May 09 '12 at 16:19

answered May 09 '12 at 14:44

vyegorov

20,303
6
57
72

score 29 · Answer 4 · edited Mar 02 '20 at 07:10

29

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

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

edited Mar 02 '20 at 07:10

Abel Callejo

11,749
8
60
74

answered Jun 01 '15 at 20:22

Jade Hamel

1,290
1
15
30

2

For me (9.6.2) this was the only thing that worked, all the other answers failed. – Jasper de Vries Jul 14 '17 at 13:20
2

Couldn't you add a `WHERE != NULL`? – Matthieu Mar 12 '18 at 17:03

score 15 · Answer 5 · edited Jul 25 '14 at 17:12

15

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

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

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

edited Jul 25 '14 at 17:12

Mike Guthrie

3,979
2
27
46

answered May 09 '12 at 14:46

niko

1,696
12
11

1

This should work, but it should be an exact equivalent of the less verbose `NULLIF()` approach. The standard actually defines NULLIF as a form of the CASE predicate. – kgrittn May 09 '12 at 16:13

score 13 · Answer 6 · answered Jun 22 '18 at 08:59

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

Abel Callejo · Answer 7 · 2020-03-18T08:17:38.977

6

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 safely type casting any string into an integer.

Solution

Create a user-defined function inspired by PHP's intval() function.

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;

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

edited Mar 18 '20 at 08:17

answered Mar 02 '20 at 08:40

Abel Callejo

11,749
8
60
74

Would fail for very large numbers. It'd work perfectly if you catch exceptions as well – jaisonDavis Jul 19 '21 at 12:45
@jaisonDavis the topic is about integer that's why it is expected that this solution would fail for very large numbers. You might want to consider creating a similar function to this solution eg: `bigintval()` and for that function replace anything that says `integer` and replace it with `bigint`. The fail is probably just about the [data type](https://www.postgresql.org/docs/9.5/datatype-numeric.html) limits. – Abel Callejo Jul 20 '21 at 00:47

score 3 · Answer 8 · edited Jan 10 '20 at 12:46

3

you can use this query

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

edited Jan 10 '20 at 12:46

slfan

8,665
115
63
77

answered Jan 10 '20 at 12:21

Abdul Quadir

51
3

score 0 · Answer 9 · answered Apr 14 '21 at 06:53

0

And if your column has decimal points

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

answered Apr 14 '21 at 06:53

Omari Victor Omosa

2,682
1
23
41

Konstantin Chemshirov · Answer 10 · 2022-02-18T12:46:38.460

This works for me:

select (left(regexp_replace(coalesce('<column_name>', '0') || '', '[^0-9]', '', 'g'), 8) || '0')::integer

For easy view:

select (
    left(
        regexp_replace(
            -- if null then '0', and convert to string for regexp
            coalesce('<column_name>', '0') || '',
            '[^0-9]',
            '',
            'g'
        ),      -- remove everything except numbers
        8       -- ensure ::integer doesn't overload
    ) || '0'    -- ensure not empty string gets to ::integer
)::integer

Typecast string to integer

10 Answers10

Common issue

Problem

Solution

Usage

Linked