0

I'm trying to perform a SQL Query with a LIKE clause such as:

SELECT
    :
FROM
    mytable
    :
WHERE
    code LIKE 'SOME_ID (%'

to match codes like "SOME_ID (5)", "SOME_ID (63)", etc

But getting the error

Error saving the Query field.An unmatched parenthesis occurs in the query.

Assume I have to escape the parenthesis somehow, but nothing I've tried seems to be able to do so.

Tried some of the standard SQL escaping methods

WHERE
    code LIKE 'SOME_ID \(%'

and

WHERE
    code LIKE 'SOME_ID (()%'

and

WHERE
    code LIKE 'SOME_ID \(%' ESCAPE '\'

None seem to work.

How do I escape special characters/parenthesis in SFMC SQL Queries?

TIA!

7
  • Where are you attempting to create this SOQL query, as I tried a SOQL query using a single parenthesis within a LIKE clause and it worked just fine?
    – Paul N
    Aug 17, 2022 at 16:53
  • Is the rest of your query (your ":" ": mytable" lines) correct?
    – Paul N
    Aug 17, 2022 at 17:00
  • Perhaps a similar issue as salesforce.stackexchange.com/questions/373058/…
    – Paul N
    Aug 17, 2022 at 17:01
  • @PaulN this question is about Marketing Cloud and SQL, not SOQL
    – zuzannamj
    Aug 17, 2022 at 17:46
  • 1
    Are you trying this in Query Studio or Query Activity? QS is known to give some weird errors that are not found in Query Activities. Aug 17, 2022 at 17:55

1 Answer 1

6

You can solve this by using the CHAR() function in SQL. The number for open parantheses is 40.

SELECT 
 :
FROM 
 :
WHERE field LIKE 'SOME_ID ' + CHAR(40) + '%'

Will look for 'SOME_ID (%' as you requested. This removes any syntax confusion the processors might be running into.

1
  • This solution did work for me. Thank you!
    – Carl S
    Aug 30, 2022 at 17:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .