Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Prefixing with special character  in the column output generated from vsql

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.

Comments

  • 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)
    image

    Now see you example (where charset are different)

    image

    I hope this helped.

  • 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.
    image


    Thanks
    Rameshimage
  • 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 :
    [[email protected] ~]$ 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)
     

  • 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.


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

  • I am concatenating the pound symbol like below

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


  • 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)
  • 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.


  • Ok i think this is what you mean![[email protected] ~]$ vsql -dtest -Udbadmin -wtest -c "select  '£'||CAST (ROUND(123) AS NUMERIC(15,2)) as 'Example using £' from dual;" -o /tmp/testq
    [[email protected] ~]$ cat /tmp/testq
     Example using £
    -----------------
     £123.00
    (1 row)
  • :) yes.. but to see that character, you need to run "od -c filename" or open in Excel.

    Thanks.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.