The question asked was to develop a logic for searching for few wildcard characters [,],^,%,_ (ie )based pattern (for ex. [ ] ^ _ %) within a table column and return any rows that contain this pattern within the column.
Usual method to search for a set of characters is to enclose them within []. But here the tricky part is that the characters [ and ] themselves are among characters to be searched. Also _,^ etc are also used inside [] to denote wildcards as explained here.
The way to search for these characters is to escape them to enable the parser to not consider them as wildcard characters and include them as a part of the search string.
This can be done in two ways as explained below
Method 1: Enclosing the character within [] to escape.
See this example below
SELECT *
FROM
(
SELECT '[ ] ^ _ %' AS string_value UNION ALL
SELECT '[ ] ^ _ %anjhd' UNION ALL
SELECT 'jkhjkhnb[ ] ^ _ %' UNION ALL
SELECT 'hjjhgjhg[ ] ^ _ %anjhd'
)t
WHERE patindex ('%[[ ]] ^ _ [%]', string_value) >0
output
--------------------------------------
string_value
--------------------------------------
[ ] ^ _ %
jkhjkhnb[ ] ^ _ %As you see from above the characters to be searched for are enclosed within [] to regard them as search characters. The output clearly indicates that the parser regarded them as search characters and returned result with strings that contain the pattern [ ] ^ _ %
Method 2 Using the ESCAPE keyword
This is another method that can be used with LIKE operator and enables a character to be defined as escape sequence. Then any wildcard character can be escaped inside pattern using this escape character as shown below.
SELECT *
FROM
(
SELECT '[ ] ^ _ %' AS string_value UNION ALL
SELECT '[ ] ^ _ %anjhd' UNION ALL
SELECT 'jkhjkhnb[ ] ^ _ %' UNION ALL
SELECT 'jkhjkhnb[ ][ ^ _ %' UNION ALL
SELECT 'hjjhgjhg[ ] ^ _ %anjhd'
)t
WHERE string_value LIKE '%|[ ] ^ _ |%' ESCAPE '|'
output
-------------------------------
string_value
-------------------------------
[ ] ^ _ %
jkhjkhnb[ ] ^ _ %In the above scenario character | is defined as the escape character and is used to precede each of the characters [,],^ etc to regard them as a search string and not as wildcard character. You can use any character as escape character by specifying them along with ESCAPE clause.
Hope this explains the methods to be used for searching patterns containing wildcard based characters. As always feel free to revert with any questions/clarification on the above. Thanks to the original poster sigmas as well for bringing up this question in the forum.