How To Quote a Single Quote in Netezza SQL?
The short answer is to use four single quotes (””), which will result in a single quote within the select statement results.
How to Assemble the SQL to Quote a Single Quote in a SQL Select Statement
Knowing how to construct a list to embed in a SQL where clause ‘in’ list or to add to an ETL job can be a serious time saver eliminating the need to manually edit large lists. In the example below, I used the Select result set to create a rather long list of values, which needed to be included in an ELT where clause. By:
- Adding the comma delimiter (‘,’) and a Concatenate (||) on the front
- Followed by adding a quoted single Quote (entered as four single quotes (””)) and a Concatenate (||)
- The Field I which to have delaminated and Quoted (S1.ORDER_NUM)
- And closed with a quoted single Quote (entered as four single quotes (””))
This results in a delimited and quoted list ( ,’116490856′) which needs only to have the parentheses added and the first comma removed, which is much less work than manually editing the 200 item that resulted from this select.
‘,’||””|| S1.ORDER_NUM||”” as Quoted_Order_Number
FROM Sales S1
- Netezza / PureData – How to build a multi-table drop command from a select
- Netezza / PureData – Aginity for Netezza shortcut key list
- Netezza / PureData – Table Describe SQL
- Aginity For Netezza – How to Generate DDL
- Netezza / PureData – Substring Function Example
- Netezza / PureData – Substring Function On Specific Delimiter