Page 1 of 1

commas and % in the dataset

Posted: Sat Nov 12, 2016 7:49 pm
by Beg1nner
Hi Team,

I have a dataset which contains below items. I have used integer and numeric. But Vertica is rejecting data. What is best option to store such values as integers/float and not varchar?

1,234
54.1%

Re: commas and % in the dataset

Posted: Sun Nov 13, 2016 7:20 am
by JimKnicely
Hi,

You can use the FILLER option of the COPY command with a regular expression function. Something like the following:

Code: Select all

dbadmin=> create table t (c numeric);
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/t.txt
1,234
54.1%

dbadmin=> copy t(c_f filler varchar(100), c as REGEXP_REPLACE(c_f, '[^\d.-]', '')::numeric) from '/home/dbadmin/t.txt';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select * from t;
          c
----------------------
 1234.000000000000000
   54.100000000000000
(2 rows)