With the use of wildcards in SQL code comes great power, but also great responsibility. I do not want to describe all the possibilities which arise with using wildcards. We can easily find it here.
I would look at the whole thing the other way around. And that’s when wildcards appear in our code without us noticing it. For example, we only want to filter records that contain product a, which is represented as the string ‚product_a‘.
SELECT * FROM PRODUCT
WHERE PRODUCT LIKE ‚%PRODUCT_A%‘
The pitfall is that the underscore is a reserved wildcards character. Thus, the code written this way selects all records that contain the string ‘product’, followed by any character (wildcard’_ ‘), followed by the string ‘a’ and zero or more of any other characters. In our case, therefore, in addition to the desired product ‚product_a ’, e.g., the string product ‚product and others‘ may appear in the output, which is not desirable.
- % Represents zero or more characters
- _ Represents a single character
So, how to “escape” it. There are more possibilities, but in practice, the following worked best for me. The advantage here is that based on my experience, this method works in all SQL syntaxes.
SELECT * FROM PRODUCT
WHERE PRODUCT LIKE ‚%PRODUCT$_A%‘ ESCAPE '$'
ESCAPE is used together with the LIKE operator and indicates a wildcard character to be skipped and used as a standard character after using the escape character (in our case, $). As a result we get only records containing ‚product_a‘. and the underscore is taken as a standard character.
Finally, it’s worth mentioning that SQL wildcards are not regular expressions. Although both can often achieve the same results, it is best not to confuse the two.