Special characters can sometimes pose a challenge when it comes to pattern matching in SQL Server. These characters can represent different values at different times, making it difficult to query for specific patterns. In this blog post, we will explore some tips and techniques for querying special characters using wildcards in SQL Server.

Finding Literal % Signs with Wildcards

One common special character is the percentage sign (%), which is often used as a wildcard in SQL Server. However, if you want to search for a literal percentage sign with wildcards on either side, you need to use a different approach.

Instead of using the percentage sign as a wildcard, you can enclose it in square brackets to treat it as a literal percentage symbol. For example, if you want to find grants with a percentage sign in their names, you can use the following query:

SELECT *
FROM [GRANT]
WHERE GrantName LIKE '%[%]%'

This query will return only the grants that have a percentage sign in their names.

Finding Literal _ Signs with Wildcards

Another special character that can be tricky to query is the underscore (_), which is also a wildcard in SQL Server. If you want to find grants with an underscore as the second letter in their names, you can use the following query:

SELECT *
FROM [GRANT]
WHERE GrantName LIKE '_A%'

This query will return grants where the second letter is ‘A’ and any number of characters follow.

Similar to the approach used for the percentage sign, you can enclose the underscore in square brackets to treat it as a literal underscore character. For example, if you want to find grants with underscores in their names, you can use the following query:

SELECT *
FROM [GRANT]
WHERE GrantName LIKE '%[_]%'

This query will return only the grants that have an underscore in their names.

Conclusion

Querying special characters with wildcards in SQL Server can be challenging, but by using square brackets to treat the special characters as literal symbols, you can overcome these challenges. Remember to enclose the special characters in square brackets to ensure they are treated as literals in your queries.