Well, after writing so many queries in MS SQL over the years, normally I don't have to think to form queries that have conditions with wildcard characters for pattern matching in LIKE clause. "%" is somewhat favorite and have used it countless times. But when I needed it today I was surprised to notice so far I had never required to write a query to match wildcard as a literal character.
Today I needed to find all records in a table that contained string "%" in a field title and then replace "%" sign with word "-percent"
Doing this turned out to be much simpler than I thought. Just enclose the wildcard character in brackets to use it as a literal character
My select clause looked something like below
Select * from tablename Where title like '%[%]%'
Notice the percent sign that I would like to be checked as literal in title is enclosed in [] while % on both sides of [%] is used as wildcard to match any string before and after % sign
For replacing wildcard percent simply use the it as you would use any other character
update tablename set title = replace(title, '%', '-Percent' ) Where title like '%[%]%'
Note: Enclosing wilchard character in [] worked for me but as per microsoft article at link below
to search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided
So my Select statement should have been
select * from tablename where title like '%!%%' ESCAPE '!'
Reference: http://msdn2.microsoft.com/en-us/library/ms179859.aspx
