How can we help you today?
In one of my apps user entered [ (open bracket) in the search field and the query started to give errors. Unterminated pattern
Is there a way around this
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?
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.