How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL

Question

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   SELECT myfield  integer FROM mytable   yields ERROR   invalid input syntax for integer      How can I query a cast and have 0 in case of error during the cast in postgres

User · Answer

I had the same sort of need and found this to work well for me (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.

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

Then text/varchar values like "b3ad5" will also give numbers

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:

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":

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

User · Answer

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 0 if there are no digits in the input string   SELECT parse int  test12 3test     will return 123

User · Answer

The following function does   use a default value  error result  for not castable results e g abc or 999999999999999999999999999999999999999999 keeps null as null trims away spaces and other whitespace in input values casted as valid bigints are compared against lower bound to e g enforce positive values only   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

User · Answer

This should also do the job but this is across SQL and not postgres specific   select avg cast mynumber as numeric   from my table

User · Answer

I also have the same need but that works with JPA 2 0 and Hibernate 5 0 2   SELECT p FROM MatchProfile p WHERE CONCAT p id         keyword   Works wonders  I think it works with LIKE too

User · Answer

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    The above mentioned expression yields NULL for NULL-values in myfield and 0 for empty strings  This exact behaviour may or may not fit your use case    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  integer will result in an error message   Hope this helps

User · Answer

I found the following code easy and working  Original answer is here https   www postgresql org message-id 371F1510 F86C876B sferacarta com  prova  gt  create table test t text  i integer   CREATE  prova  gt  insert into test values  123  123   INSERT 64579 1  prova  gt  select cast i as text  cast t as int from test  text int4 ---- ---- 123  123  1 row    hope it helps

User · Answer

Finally I manage to ignore the invalid characters and get only the numbers to convert the text to numeric  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

User · Answer

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    If you want zeros instead of not selecting  then a CASE statement should work   SELECT CASE WHEN myfield E    d    THEN myfield  integer ELSE 0 END FROM mytable

User · Answer

You could also create your own conversion function  inside which you can use 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

User · Answer

SELECT CASE WHEN myfield    THEN 0 ELSE myfield  integer END FROM mytable  I haven t ever worked with PostgreSQL but I checked the manual for the correct syntax of IF statements in SELECT queries

User · Answer

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

User · Answer

Matthew s answer is 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   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    This returns 0 for an empty string and NULL for any other invalid input  It can easily be adapted for any data type conversion   Entering an exception block is substantially more expensive  If empty strings are common it 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

User · Answer

SUBSTRING may help for some cases  you can limit the size of the int   SELECT CAST SUBSTRING  X12312333333333       d  1 9     AS integer

[sql] How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

Examples related to sql

Examples related to postgresql

Examples related to casting