CODEWITHSUNDEEP
postgresql
Vijay DJ
Vijay DJ

Reputation: 2129

Typecast string to integer

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.

Upvotes: 193

Views: 538312

Answers (11)

Bedram Tamang
Bedram Tamang

Reputation: 4424

The perfect solution for me is to use nullif and regexp_replace

SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;

Above solution consider the following edge cases.

  1. String and Number: only the regexp_replace function perfectly converts into integers.
SELECT NULLIF(REGEXP_REPLACE('string and 12345', '[^0-9]', '', 'g'), '')::bigint;
  1. Only string: regexp_replace converts non-string characters to empty strings; which can't cast directly to integer so use nullif to convert to null
SELECT NULLIF(REGEXP_REPLACE('only string', '[^0-9]', '', 'g'), '')::bigint;
  1. Integer range: Converting a string into integer may cause out of range for type integer error. So use bigint instead
SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;

Upvotes: 2

Konstantin Chemshirov
Konstantin Chemshirov

Reputation: 195

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

Upvotes: 0

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

And if your column has decimal points

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

Upvotes: 0

Abel Callejo
Abel Callejo

Reputation: 14969

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

Upvotes: 12

Jade Hamel
Jade Hamel

Reputation: 1430

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

Upvotes: 33

Abdul Quadir
Abdul Quadir

Reputation: 61

you can use this query

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

Upvotes: 3

Igor Ostrovsky
Igor Ostrovsky

Reputation: 180

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

Upvotes: 17

niko
niko

Reputation: 1866

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

Upvotes: 17

tatty
tatty

Reputation: 866

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;

Upvotes: 80

vyegorov
vyegorov

Reputation: 22925

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.

Upvotes: 40

Frank Heikens
Frank Heikens

Reputation: 127556

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

SELECT
    NULLIF(your_value, '')::int

Upvotes: 201

Related Questions