commas and % in the dataset

Moderator: NorbertKrupa

Post Reply
Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

commas and % in the dataset

Post by Beg1nner » Sat Nov 12, 2016 7:49 pm

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%

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1768
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: commas and % in the dataset

Post by JimKnicely » Sun Nov 13, 2016 7:20 am

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)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica”