Skip to main content

Prefixing with special character  in the column output generated from vsql

  • May 21, 2014
  • 10 replies
  • 34 views
ramesh_1
Forum|alt.badge.img
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.

    10 replies

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 21, 2014
    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.

    ramesh_1
    Forum|alt.badge.img
    • ramesh_1
    • Author
    • Participating Frequently
    • May 21, 2014
    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
    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 21, 2014
    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
    Forum|alt.badge.img
    • ramesh_1
    • Author
    • Participating Frequently
    • May 21, 2014
    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
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 22, 2014
    Not sure i understand what you are trying to say, but the currency is based on your actual locale values.

    ramesh_1
    Forum|alt.badge.img
    • ramesh_1
    • Author
    • Participating Frequently
    • May 22, 2014
    I am concatenating the pound symbol like below

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


    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 22, 2014
    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
    Forum|alt.badge.img
    • ramesh_1
    • Author
    • Participating Frequently
    • May 22, 2014
    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
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 22, 2014
    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
    Forum|alt.badge.img
    • ramesh_1
    • Author
    • Participating Frequently
    • May 22, 2014
    :) yes.. but to see that character, you need to run "od -c filename" or open in Excel.

    Thanks.
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement