His reply went to a ticket, but indicated that he's using LIKE, so for anyone else who runs into this...

The pattern-matching in the LIKE function has a few special characters.  % matches on any number of any characters.  _ matches any single character.  And [abc] matches on any of the characters inside the brackets--in this case a b or c.  So, if a [ is used by itself, the pattern can fail to parse or can otherwise behave differently than the user intended or expected.

There are a couple ways to work around the special meaning of [.  You could use the ESCAPE keyword (part of the LIKE function) to define an escape character and then use it to escape the left-bracket (using Replace), such as:  WHERE vendor LIKE 'BETTS\[' ESCAPE '\'  (Note that the argument to the ESCAPE keyword must be a string literal, not a variable, from what I'm seeing in the code.)

Or you could try using [ as the escape character as in:  WHERE vendor LIKE 'BETTS[[' ESCAPE '['  and then use a Replace to replace each [ with [[ in the pattern argument.  However, in testing this approach I think we've found a bug where the double-escape sequence will also incorrectly escape whatever character follows the [[ instead of only escaping the second [ character.  This would also affect attempts to include any other escape character by escaping itself such as \\.  If you tried to use a special character like % or _ (or even [ ) intentionally immediately after a double-escape sequence, then the special character would likely be treated as literal rather than special.  If you're going to escape all special characters this may be less of a problem.

The other possible approach would be to use the interpretation of [ ] themselves to escape any left-bracket characters (not using the ESCAPE keyword).  If you use patternString.Replace("[", "[[]") then it should convert the user's use of any [ into a pattern that will match specifically on a [ character.  This approach is also not without potential pitfalls, but it appears to be safer.  Assuming you also want to escape use of _ and % characters you could do patternString.Replace("[", "[[]").Replace("_", "[_]").Replace("%", "[%]") to replace all three--and make sure to replace [ before adding more of them in replacing the others.  If you add your own % to the start or end, make sure to do so only after escaping any from the search field itself.

    -Rob

You're going to have to give more information about how the search field is used within a query.

Are you using LIKE or CONTAINS with the value of the search field used directly as the pattern argument?

    -Rob