Using escape characters with MS SQL Server

Fri, 03 Nov 2006 16:08:08 GMT

This morning I was asked to do a quick and dirty query to find all rows from a table in MS SQL Server where a particular column contained one or more pecent sign (%) charaters. Considering the '%' character is a wildcard, I obviously needed an escape sequence, but oddly I had never run across this particular problem before. After a quick search I found what appears to be a Microsoft-specific solution that suited my needs. You can use an escape character in your 'where' clause, then define that character at the end of your query by adding {escape '[your character]'} like this:

SELECT
    MyColumn
FROM
    MyTable
WHERE
    MyColumn like '%\%%'
{escape '\'}

I would like to underscore that the backslash character in my statement can be whatever you need it to be in case that is another point of conflict for you.

Grand Canyon... It's on!

Mon, 11 Jul 2005 05:00:00 GMT

My father and I decided that this was the year that we would hike the North Rim to the South Rim of the Grand Canyon. I know this seems crazy, but it is actually a paperwork challenge to be able to hike the Grand Canyon. There is an application process in which you have to fax your request in the 1st day of the month 6 months before the intended date of your trip with a detailed itinerary, including where you will be sleeping and when. We sent faxed our paperwork in and held our breath on the 1st of April, 6 months before we hoped to go in September. After no word for a month we finally contacted them, only to find out that we had been rejected. Discouraged, but not beaten, we laid out the calendar to see if there was another time that would work. We decided that November would be acceptable, albeit a little colder. It should be in the 60s in the bottom of the canyon though where we will spend the majority of our time. Once again, we laid out our itinerary and faxed in on July 1. My dad received a letter today dated July 6, 2005 telling him that they were sorry, but they were unable to accept out application. He spent a good hour walking around the house cussing and feeling terribly disappointed. A bit later my mom noticed there was another letter from the Grand Canyon dated July 7, 2005.

Dear Mr. Shuck, we have accepted your request to hike the Grand Canyon on the dates of November 1, 2 and 3, 2005. Enlcosed are your passes, which you will need to affix to your backpack and carry with you in the canyon. There is no need to stop by the back country headquarters before departing on the trail.

So in 3.5 months, I will be taking this in....

RSS feed for InstantSpot site Blog of Dave

Using escape characters with MS SQL Server

Grand Canyon... It's on!