current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 25 down vote favorite
8

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.

Kindly let me know if there is a function for the same.

share|improve this question
2  
Could you show us the error message? That would help –  Frank Heikens May 9 '12 at 14:40

5 Answers 5

up vote 25 down vote

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

SELECT
    NULLIF(your_value, '')::int
share|improve this answer
up vote 18 down vote

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;
share|improve this answer
up vote 8 down vote

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.

share|improve this answer
up vote 4 down vote

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
share|improve this answer
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 9 '12 at 16:13
up vote 0 down vote

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

SELECT REGEXP_REPLACE(COALESCE(<column>, '0'), '[^0-9]*' ,'0')::integer FROM table
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.