1

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?

Leigh
  • 28,765
  • 10
  • 55
  • 103
Patrick Schomburg
  • 2,494
  • 1
  • 18
  • 46
  • (Edit) Can you clarify? What do you mean by "no luck"? What was the result and how did it differ from what you expected? Also, when you say "blank" do you mean the value is an empty string ie "", or that it literally contains the character `'`? Side note, syntax can vary by vendor, so always your dbms with any sql questions. – Leigh Mar 06 '15 at 16:15
  • It's a coldfusion query of queries; I didn't think to include that because I tagged it (and the tag is the syntax for it) – Patrick Schomburg Mar 06 '15 at 16:33
  • No, it is my bad. I only saw the generic "sql" tag, not "qoq". I overlooked the dbtype="query". Not enough coffee ;-) – Leigh Mar 06 '15 at 16:42
  • It could be partly my fault as I added the `sql` tag. But there isn't a `query of queries` or `coldfusion-qoq` tag (at least, not that I saw). – David Faber Mar 06 '15 at 16:43
  • @DavidFaber - No worries. I still should have picked up on the dbtype="query". Added "QoQ" tag. – Leigh Mar 06 '15 at 16:45

2 Answers2

2

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 <> ''''
David Faber
  • 12,277
  • 2
  • 29
  • 40
1

cfqueryparam solves this problem equally well with q of q and database queries.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43