Thread: 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