Add double quotes to surround output from to_char function?

Moderator: NorbertKrupa

Post Reply
Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Add double quotes to surround output from to_char function?

Post by Brett » Fri Jan 10, 2014 12:29 pm

Howdy,

How can surround the output from a to_char function in double quotes? I figured the following would work... but it does not.

Code: Select all

dbadmin=> select sysdate, to_char(sysdate, '"MM/DD/YYYY"');
          sysdate           |  to_char
----------------------------+------------
 2014-01-10 06:24:56.001893 | MM/DD/YYYY
(1 row)
I'd rather not have to do this:

Code: Select all

dbadmin=> select sysdate, '"' || to_char(sysdate, 'MM/DD/YYYY') || '"';
          sysdate          |   ?column?
---------------------------+--------------
 2014-01-10 06:26:55.50526 | "01/10/2014"
(1 row)
Anyone know what I am doing wrong in the format string? Thanks in advance!

Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Re: Add double quotes to surround output from to_char functi

Post by Brett » Fri Jan 10, 2014 12:57 pm

Ok, I thought I found the answer in the Vertica 7 SQL Reference Guide on page 341, but using the suggested solution I end up with extra backslashes:

Code: Select all

dbadmin=> select current_date "current_date", to_char(current_date, '\\"MM/DD/YYYY\\"');
 current_date |    to_char
--------------+----------------
 2014-01-10   | \"01/10/2014\"
(1 row)
Now what am I doing wrong?

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

Re: Add double quotes to surround output from to_char functi

Post by JimKnicely » Fri Jan 10, 2014 1:20 pm

Hi Brett,

Try single back slashes:

Code: Select all

dbadmin=> select current_date "current_date", to_char(current_date, '\"MM/DD/YYYY\"');
 current_date |   to_char
--------------+--------------
 2014-01-10   | "01/10/2014"
(1 row)
What are the values for your vsql session standard_conforming_strings and StandardConformingStrings database parameters?

Mine:

Code: Select all

dbadmin=> show standard_conforming_strings;
            name             | setting
-----------------------------+---------
 standard_conforming_strings | on
(1 row)

Code: Select all

dbadmin=> select get_config_parameter('StandardConformingStrings');
 get_config_parameter
----------------------
 -1
(1 row)
See this post for reference:

http://www.vertica-forums.com/viewtopic ... ings#p2716
Jim Knicely

Image

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

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

Re: Add double quotes to surround output from to_char functi

Post by JimKnicely » Fri Jan 10, 2014 1:50 pm

Brett,

FYI...

The double quote symbol in the format string has special meaning which tells Vertica to treat text enclosed by double quotes as a string literal. That is, Vertica will output the enclosed text exactly as it appears.

Example:

We can add the string literal "Current Date: " to our output like this:

Code: Select all

dbadmin=> select current_date "current_date", to_char(current_date, '"Current Date: "\"MM/DD/YYYY\"');
 current_date |          to_char
--------------+----------------------------
 2014-01-10   | Current Date: "01/10/2014"
(1 row)
If we don’t use the double quotes, Vertica will display:

Code: Select all

dbadmin=> select current_date "current_date", to_char(current_date, 'Current Date: \"MM/DD/YYYY\"');
 current_date |          to_char
--------------+----------------------------
 2014-01-10   | Cu14ent 6ate: "01/10/2014"
(1 row)
In the second query Vertica sees the rr in "Current Date: " and translates it to the last two digits of the year, and the D in "Current Date: " and translates it to the day of the week.
Jim Knicely

Image

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

Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Re: Add double quotes to surround output from to_char functi

Post by Brett » Fri Jan 10, 2014 2:33 pm

Hey, thanks, Jim!!! The single back slash gives me what I need and thanks for the explaining why :D

Post Reply

Return to “New to Vertica SQL”