Prefixing with special character  in the column output generated from vsql

ramesh_1

Hi,

I am trying to redirect the vsql output to a file with below SQL:

it is prefixing with Â£ character instead of just £ symbol. Any ideas why it is adding additional character Â.  It works fine with $ symbol.

select column1, '£'||CAST (ROUND(cost) AS NUMERIC(15,2)) from table1;

Thanks for your help.

Ramesh.

Adrian_Oprea_1

Ramesh, This type of stuff usually appears when you have your charset in your putty console of any tool you use to access Vertica db different then utf8.
Alter that restart you console(putty terminal) and try again.
See example bellow(with right charset)


Now see you example (where charset are different)



I hope this helped.

ramesh_1

Hi Andrian,

Thank you so much for quick reply. I tried your select, it shows $ even though I set en_GB for locale.


I have UTF8 in my putty terminal.



Thanks
Ramesh

Adrian_Oprea_1

Yes, Even if you alter that locale parameter Vertica will listen to the OS  LANG  and LC_ALL variable.

export LANG=en_GB.UTF8
export LC_ALL=en_GB.UTF8 

Even if you alter then you need to restart your database in order for it work.
I am not sure how you can handle different langs only by using session variables.

See example :

[dbadmin@bih001 ~]$ vsqlPassword:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit
dbadmin=> \locale pt_BR.UTF8
INFO 2567:  Canonical locale: 'pt_BR'
Standard collation: 'LROOT'
Portuguese (Brazil)  português (Brasil)
dbadmin=> SELECT TO_CHAR(12.12, 'L999G99G99G99D99');
      TO_CHAR
-------------------
 £           12.12
(1 row)
dbadmin=> \locale
pt_BR.UTF8 dbadmin=> \! echo $LANG en_GB.UTF8 dbadmin=> \! echo $LC_ALL en_GB.UTF8 dbadmin=>  

I might be wrong ! if any out there with a different opinion please fell free to add any.

For details i go over the Vertica locales in this articles article1 article2.
The site reflect my opinions only based on my daily work with Vertica(so use with care)
 

ramesh_1

Thats why I am trying add the currency symbol based on the data, I am trying to prefix the currency symbol as in select statement.

so when it is US record, it prefixes the $ with the CONCAT correctly. But when it is concatenating pound symbol it adds that extra character Â.

Thanks
Ramesh.

Adrian_Oprea_1

 Not sure i understand what you are trying to say, but the currency is based on your actual locale values.

ramesh_1

I am concatenating the pound symbol like below

select column1, '£'||CAST (ROUND(cost) AS NUMERIC(15,2)) from table1;

Adrian_Oprea_1

It should work ! it worked for me ! 

dbadmin=> select  '£'||CAST (ROUND(123) AS NUMERIC(15,2)) as "Example using £" from dual; Example using £
-----------------
 £123.00
(1 row)

ramesh_1

it shows fine there.. but put that sql in file, run vsql and redirect the output file.
Open the output file in Excel you will see the special character Â also along with pound.
Or run "od -c " on the output file, you will see 302 243 codes.

Adrian_Oprea_1

Ok i think this is what you mean![dbadmin@bih001 ~]$ vsql -dtest -Udbadmin -wtest -c "select  '£'||CAST (ROUND(123) AS NUMERIC(15,2)) as 'Example using £' from dual;" -o /tmp/testq
[dbadmin@bih001 ~]$ cat /tmp/testq
 Example using £
-----------------
 £123.00
(1 row)

ramesh_1

yes.. but to see that character, you need to run "od -c filename" or open in Excel.

Thanks.