How to Convert text values to integer values

Question

I have a problem to convert column with text values into integer values.

In table address i have column 'postcode' type TEXT. I have created a new column name 'postcode_int' type integer. In column 'postcode' some people wrote for example '330...' or '00234'. How can i check if the value is an integer.And if a value is an Integer then how to convert this values from type TEXT into type Integer and set them in column 'postcode_int' so that later i can do 'between '11111' and '99999'.

i tried to do something like this:

UPDATE adresse 
   SET postcode_int= case 
  when pg_typeof( REGEXP_REPLACE( COALESCE(trim( LEADING '0' FROM postcode), '0'), 
                                 '[^0-9]*' ,'0')::integer
                ) = integer 
  then postcode_int
  else 0 
   end;

With regards

Andrey

if i do it your way then i get this ERROR: invalid input syntax for integer: "388.." — Andrew Ramnikov, Nov 30 '15 at 11:43
So you want to convert only integer values, checking it they are in fact integers because there can be non integer values. You must add this info to your question as it is crucial! — Jorge Campos, Nov 30 '15 at 11:44
@A.Greensmith The error is because there are rows that can't be casted because it isn't integer values. — Jorge Campos, Nov 30 '15 at 11:46
yes, this why i tried case statement. case integer add to column postcode_int else add zero. — Andrew Ramnikov, Nov 30 '15 at 11:53

score 0 · Accepted Answer · answered Nov 30 '15 at 11:43

0

I believe the SQL you need is:

UPDATE adresse 
SET postcode_int = CAST(postcode AS INT)

This is based on your example.

If this answer is not clear please ask for a revision.

answered Nov 30 '15 at 11:43

A. Greensmith

355
1
8

i get this: ERROR: invalid input syntax for integer: "388.." ********** Fehler ********** ERROR: invalid input syntax for integer: "388.." SQL Status:22P02 – Andrew Ramnikov Nov 30 '15 at 11:45
2

Then this is duplicate of this question? http://stackoverflow.com/questions/2082686/how-do-i-cast-a-string-to-integer-and-have-0-in-case-of-error-in-the-cast-with-p – A. Greensmith Nov 30 '15 at 11:53
not exactly. i need to add it to a new column if it is an integer. – Andrew Ramnikov Nov 30 '15 at 11:56
"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;" – A. Greensmith Nov 30 '15 at 11:58
Then change the SELECT to an UPDATE and put the CASE against it. – A. Greensmith Nov 30 '15 at 12:03

How to Convert text values to integer values

1 Answers1