Netezza / PureData – How To Quote a Single Quote in Netezza SQL

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.

Example SQL:

SELECT Distinct

‘,’||””|| S1.ORDER_NUM||”” as Quoted_Order_Number

FROM Sales S1

How to Quote A Single Quote Example SQL
How to Quote A Single Quote Example SQL

Related Reference

What is Occam’s razor?

Acronyms, Abbreviations, Terms, And Definitions
Acronyms, Abbreviations, Terms, And Definitions

“pluralitas non est ponenda sine necessitate”

–William of Occam (1285–1347/49)

A principle in philosophy, mathematics, and science that assumptions introduced to explain something must not be multiplied beyond necessity, and, therefore, that the simplest of several hypotheses is, usually, is the best explanation of the facts.

There are two essential elements to Occam’s razor, from the Latin:

  • The Principle of Plurality – Plurality should not be used as a fact or put forward as a basis of argument, without the necessity
  • The Principle of Parsimony – It is pointless to do with more, what can be done with less

Consulting – A Consultant’s Responsibility

consultant’s responsibility, maxim, success,quote
consultant’s responsibility

Ultimately, it is each consultant’s responsibility to acquire what they need to be successful in meeting the customer’s needs.

This is not to say that the consultant needs to do everything themselves. However, they are responsible to do whatever it takes to acquire the resources may need to deliver for the customer. If that, means making late-night phone calls, repetitive phone calls, follow-up emails, tracking somebody down the hall for an impromptu meeting, that is what you must do.  In the end, regardless of the reason, you are responsible to ensure that you deliver a quality product to the customer, on time, on schedule and on budget.

Related References