Question: How to Search Special Characters in Table Column?
Answer: This is a very interesting question and lets us discuss the same today. However, before you try out the solution in this blog post, I would suggest you check with other members in your team and see if they know the answer of this question. The reason, I am asking is whenever I ask this question in the interview, I have so far seen 99% of people not knowing the solution.
Let us first create a small dataset.
CREATE TABLE TestTable (Col1 VARCHAR(100)) GO INSERT INTO TestTable VALUES ('[blog.]SQLAuthority.com') GO SELECT * FROM TestTable GO
Now you can see in the resulset there is a record with the words [blog.]SQLAuthority.com – what we need to do over here is to write a search condition on the column col1 to look for the column [blog.]. Remember the goal is to search for every row where there is [blog.] word. Let us see how we can do the same.
The natural instinct of many is usually to write the following T-SQL which does not work.
WHERE Col1 LIKE ‘%\[blog.]%’
Well, now let us see the answer which works.
SELECT * FROM TestTable WHERE Col1 LIKE '%$[blog.]%' ESCAPE '$' GO
If you run the above command it will return us all the row which contains [blog.] words. Even though I have asked many, I have yet to receive a good answer to this question. I think not many people know the keyword ESCAPE. You can replace $ with any other special character and write that into ESCAPE word and it script will search for the words right after the specified escape words.
For example instead of $ you can also use ! .
SELECT * FROM TestTable WHERE Col1 LIKE '%![blog.]%' ESCAPE '!' GO
Did you know the keyword ESCAPE before you read in this blog post?
Reference: Pinal Dave (https://blog.sqlauthority.com)