Search code examples
sqlcoldfusionqoq

Escape apostrophe in Query of Query

I'm trying to do this: 

<cfquery name="GetAccountsAndStocks" dbtype="query">
    Select STOCK, CUST_NUMBER
      From GetExtractionData
     WHERE CUST_NUMBER NOT LIKE  '\''
</cfquery>

The cust_number is either ' (for blank) or ' followed by a 10 character string.

I thought I should escape the ', but it doesn't work. How can I do this?

Solution

  • To escape a single quote, use '' (two single quotes), e.g.:

    WHERE cust_number NOT LIKE ''''

    However, I'm not too familiar with the use of NOT LIKE in query of queries; ordinarily one would use a wildcard (such as %):

    WHERE cust_number NOT LIKE '%''%'

    In your case you say cust_number is a single quote if it's intended to be blank. You would not use NOT LIKE for that, but just <>:

    WHERE cust_number <> ''''