Обсуждение: output a single and double quote in a string

Hi All,

I am trying to create output that contains a dimension and the user would like
to see single quote for feet and double quote for inches.  I have not as yet
stumbled on the solution.

 SELECT length_ft::text || \' ||
        length_in::text || \" AS length_dim,
        width_ft::text || \' ||
        width_in::text || \" AS width_dim
   FROM sales_order.tbl_net_production;

Kind Regards,
Keith

----- Original Message -----
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "PostgreSQL Novice" <pgsql-novice@postgresql.org>
Sent: Friday, March 18, 2005 3:41 PM
Subject: [NOVICE] output a single and double quote in a string


> Hi All,
>
> I am trying to create output that contains a dimension and the user would
> like
> to see single quote for feet and double quote for inches.  I have not as
> yet
> stumbled on the solution.

How about

SELECT length_ft::text || chr(39)
        length_in::text || \' " \' AS length_dim,
        width_ft::text || chr(39) ||
        width_in::text || \' " \' AS width_dim
   FROM sales_order.tbl_net_production;

>
> SELECT length_ft::text || \' ||
>        length_in::text || \" AS length_dim,
>        width_ft::text || \' ||
>        width_in::text || \" AS width_dim
>   FROM sales_order.tbl_net_production;
>
> Kind Regards,
> Keith
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

On Fri, Mar 18, 2005 at 05:13:32PM -0600, George Weaver wrote:
>
> SELECT length_ft::text || chr(39)
>        length_in::text || \' " \' AS length_dim,
>        width_ft::text || chr(39) ||
>        width_in::text || \' " \' AS width_dim
>   FROM sales_order.tbl_net_production;

Not quite -- that produces errors because of a missing || operator
and single quotes that shouldn't be escaped (at least not in a
standalone query).  Perhaps you meant this:

SELECT length_ft::text || chr(39) ||
       length_in::text || '"' AS length_dim,
       width_ft::text || chr(39) ||
       width_in::text || '"' AS width_dim
  FROM sales_order.tbl_net_production;

Other examples:

SELECT '\'' AS single_quote, '"' AS double_quote;
SELECT '''' AS single_quote, '"' AS double_quote;
SELECT $$'$$ AS single_quote, $$"$$ AS double_quote;  -- 8.0 and later

See "String Constants" in the "SQL Syntax" chapter of the documentation:

http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html#SQL-SYNTAX-CONSTANTS

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi Michael,

You're right, I did drop the || operator at the end of the first line when
copying Keith's code.  I also made the assumption (perhaps mistakenly) that
Keith was using this in plpgsql, hence the escaped '.

Regards,
George

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "George Weaver" <gweaver@shaw.ca>
Cc: <KeithW@narrowpathinc.com>; "PostgreSQL Novice"
<pgsql-novice@postgresql.org>
Sent: Friday, March 18, 2005 8:56 PM
Subject: Re: [NOVICE] output a single and double quote in a string


> On Fri, Mar 18, 2005 at 05:13:32PM -0600, George Weaver wrote:
>>
>> SELECT length_ft::text || chr(39)
>>        length_in::text || \' " \' AS length_dim,
>>        width_ft::text || chr(39) ||
>>        width_in::text || \' " \' AS width_dim
>>   FROM sales_order.tbl_net_production;
>
> Not quite -- that produces errors because of a missing || operator
> and single quotes that shouldn't be escaped (at least not in a
> standalone query).  Perhaps you meant this:
>
> SELECT length_ft::text || chr(39) ||
>       length_in::text || '"' AS length_dim,
>       width_ft::text || chr(39) ||
>       width_in::text || '"' AS width_dim
>  FROM sales_order.tbl_net_production;
>
> Other examples:
>
> SELECT '\'' AS single_quote, '"' AS double_quote;
> SELECT '''' AS single_quote, '"' AS double_quote;
> SELECT $$'$$ AS single_quote, $$"$$ AS double_quote;  -- 8.0 and later
>
> See "String Constants" in the "SQL Syntax" chapter of the documentation:
>
> http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html#SQL-SYNTAX-CONSTANTS
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

On Fri, 18 Mar 2005 22:15:26 -0600, George Weaver wrote
>
> ----- Original Message -----
> From: "Michael Fuhr" <mike@fuhr.org>
> To: "George Weaver" <gweaver@shaw.ca>
> Cc: <KeithW@narrowpathinc.com>; "PostgreSQL Novice"
> <pgsql-novice@postgresql.org>
> Sent: Friday, March 18, 2005 8:56 PM
> Subject: Re: [NOVICE] output a single and double quote in a string
>
> > On Fri, Mar 18, 2005 at 05:13:32PM -0600, George Weaver wrote:
> >>
> >> SELECT length_ft::text || chr(39)
> >>        length_in::text || \' " \' AS length_dim,
> >>        width_ft::text || chr(39) ||
> >>        width_in::text || \' " \' AS width_dim
> >>   FROM sales_order.tbl_net_production;
> >
> > Not quite -- that produces errors because of a missing || operator
> > and single quotes that shouldn't be escaped (at least not in a
> > standalone query).  Perhaps you meant this:
> >
> > SELECT length_ft::text || chr(39) ||
> >       length_in::text || '"' AS length_dim,
> >       width_ft::text || chr(39) ||
> >       width_in::text || '"' AS width_dim
> >  FROM sales_order.tbl_net_production;
> >
> > Other examples:
> >
> > SELECT '\'' AS single_quote, '"' AS double_quote;
> > SELECT '''' AS single_quote, '"' AS double_quote;
> > SELECT $$'$$ AS single_quote, $$"$$ AS double_quote;  -- 8.0 and later
> >
> > See "String Constants" in the "SQL Syntax" chapter of the documentation:
> >
> >
http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html#SQL-SYNTAX-CONSTANTS
> >
> > --
> > Michael Fuhr
> > http://www.fuhr.org/~mfuhr/
> >
> Hi Michael,
>
> You're right, I did drop the || operator at the end of the first
> line when copying Keith's code.  I also made the assumption (perhaps
> mistakenly) that Keith was using this in plpgsql, hence the escaped '.
>
> Regards,
> George

George & Michael,

Thank you both for the pointers.  The query is now working and producing the
results that I needed.

Kind Regards,
Keith