SOQL Gotchas

There are a few things that trip me up when writing SOQL. I hope that sharing these gotchas will save you from making the same mistakes. Maybe writing this post will even help me remember this stuff too 🤔
  1. Single Quotes
  2. NOT LIKE
  3. Filtering a DateTime with a Date

Single Quotes

It seems silly, but single quotes always trip me up. For example, I get an unexpected token error when querying for an Account named: Brett's Awesome Company


This error happens because SOQL only sees: WHERE Name = 'Brett'.
The s Awesome Company' part is hanging off the end, making it an unexpected token.

We need to escape the single quote with a backslash (\) so SOQL can see WHERE Name = 'Brett\'s Awesome Company'.


SOQL requires you to escape single quotes and a few other characters. A complete list with some examples can be found in the docs.

NOT LIKE

The LIKE comparison operator in SOQL will let you filter with a wildcard by using either the % or _ character. This is a powerful technique.

Trouble comes for me when I want to filter out records with a wildcard. The trick here is to apply a NOT to the wildcard filter. This tells the query to return all records where the condition is NOT true.

For example, this is how we would filter out all Contacts that have a First Name starting with B:


If we want to filter out B and C, we can wrap multiple wildcard filters in parentheses and put a NOT before the whole thing. This works like a charm:

Filtering a DateTime with a Date

Have you ever needed to query for records created on a certain date? You could do this:

But wow, talk about painful 😰

Instead, try passing your DateTime field into the DAY_ONLY() date function. This will return the date portion of the value, letting you do something like this:

Much better 😎

Continued Learning

The best way to learn is by getting your hands dirty! If you don't write enough SOQL as part of your day-to-day workflow, I recommend using questions on the Salesforce StackExchange as examples to practice with. Who knows, you might even answer some questions! 

When I get stuck on something, I tend to go to the SOQL docs directly. Here are a few pages I bookmark for quick reference:

Comments

  1. The representation of this article is actually superb. I think this is a genuinely beneficial and instructive article for everyone. I appreciate this kind of writing. Thankful to you for sharing an article like this.Indonesia Export Data

    ReplyDelete

Post a Comment

Popular posts from this blog

Joins in SOQL

SOQL from the Command Line

Salesforce Lighting Migration: Plan for Success