No Returns When Using like Statement

Kinetic ERP
1

Problem:

BAQs and like statements are not working against tilda delimited fields with spaces in them. The items in the field are set up as follows “CUST_12345 [A]~CUST_12346 [Z]”.

Recreation Steps:

  1. Pull the table into the query designer
  2. Add a new sub query criteria against the field
  3. Set operation to LIKE
  4. Set expression to ‘%CUST_12345 [A]%’

Results:

When I run the query like this, I get 0 results even though I know that there are rows in the data that match.

Side Tangent:

If I remove the subquery criteria and run the BAQ wide open, then apply a filter in the top right corner of the grid with the same string in there, I get my returns.

Question:

What have I done wrong? What am I missing? Is it a simple case of the Mondays? Let me know.

2

The brackets are screwing you up.

Show ChatGPT that.

3

Why Your BAQ LIKE Isn’t Matching

The Problem

Your data contains values like:

CUST_12345 [A]~CUST_12346 [Z]

You might expect this BAQ filter to work:

LIKE '%CUST_12345 [A]%'

But it returns no results.

Why

The LIKE operator treats certain characters as special pattern characters instead of normal text.

In your search string:

  • _ means any single character
  • [A] means the character “A”, not the literal text [A]

So SQL is not searching for:

CUST_12345 [A]

It is searching for something closer to:

CUST12345 A

which doesn’t exist in your data.

The Fix

Escape the special characters so SQL treats them as literal text:

LIKE '%CUST[_]12345 [[]A[]]%'

Escape Reference

To Match Use
_ [_]
[ [[]
] []]

Why the Grid Filter Works

The grid filter in the BAQ results window uses different filtering logic and automatically handles these characters for you.

The BAQ designer sends the expression directly to SQL Server, where LIKE follows SQL pattern-matching rules.

Summary

The problem is not the tilde (~) or the spaces.

The issue is that LIKE treats _, [, and ] as special characters. Escape them when you want to search for the literal text.

4

@klincecum Yo! Thanks for the quick reply. I didn’t know that was a thing… Has it always been like this?!

5

Yes.

Just some of the nuances you’ll run into with SQL.

6

Fix for those who want to handle something similar in a parameter

‘%’ + REPLACE(REPLACE(REPLACE(@Search,‘[’,‘[[]’),‘%’,‘[%]’),‘_’,‘[_]’) + ‘%’

@klincecum Thanks a ton, man!