how to convert a varchar field value into an integ

Question

for example in a table one field is character varying so how to convert that character varying to an Integer type, what exactly is I need to get the return value in Integer datatype

Database : postgresql-9.2

Answer 1

SELECT mycolumn::integer FROM mytable WHERE something 

SELECT CASE WHEN mycolumn = NULL THEN NULL ELSE mycolumn :: Integer END
FROM mytable WHERE something 

Answer 2

i would suggest to make Functions to do the job i.e

    CREATE OR REPLACE FUNCTION chartoint(charparam character varying)
    RETURNS integer AS
    $BODY$
    SELECT CASE WHEN trim($1) ~ '[0-9]+' THEN CAST(trim($1) AS integer) ELSE NULL END;
    $BODY$
    LANGUAGE sql IMMUTABLE STRICT

Answer 3

You may try like this:

SELECT NULLIF(your_value, '')::int

or extend that like thats

SELECT CAST(coalesce(column_name, '0') AS integer) as Value
from table_name