{"id":457,"date":"2013-01-24T09:00:31","date_gmt":"2013-01-24T16:00:31","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=457"},"modified":"2014-12-17T10:01:31","modified_gmt":"2014-12-17T17:01:31","slug":"escaping-from-an-underscore-in-a-sql-server-wildcard-like-search","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2013\/01\/escaping-from-an-underscore-in-a-sql-server-wildcard-like-search\/","title":{"rendered":"Escaping From An Underscore In A SQL Server Wildcard \/ LIKE Search"},"content":{"rendered":"

There are two different ways to perform a wildcard (aka LIKE) search in SQL Server to find a word with an \"_\" underscore. Both options are outlined at the end of the article after a reference to the cult classic Mystery Science Theater 3000: Escape From The Bronx.<\/p>\n

What the heck?!? SQL Server's wildcard search is broken! I was writing a query against sys.sql_modules to find any objects which were referencing the view \"v_customer\" via a linked server:<\/p>\n

SELECT OBJECT_NAME(sm.object_id)\r\nFROM sys.sql_modules AS sm\r\nWHERE sm.definition LIKE '%v_customer%'<\/pre>\n
A number of objects were returned which didn't seem to have the view in them. After running the same query a few times hoping for a different result (surprisingly that didn't work) I put my thinking cap on. Somewhere deep in the bowels of my brain (can a brain have bowels?) I recalled that an underscore is actually a wildcard character in SQL Server 2000, SQL Server 2005, SQL Server 2008, and onward.<\/p>\n
So, how do you do a wildcard query to look for a word or phrase with an underscore? You must escape! (As in Escape From The Bronx! The first Mystery Science Theater 3000<\/a> movie I ever saw.\u00a0 I was rolling on the ground laughing. You gotta check this thing out.)<\/p>\n
\"Maybe<\/a>
Maybe he can get a haircut after he escapes?<\/figcaption><\/figure>\n
If you need to perform a wildcard search for a term with an underscore there are two options.\u00a0 For the first option ou can enclose the underscore with square brackets like the following example:<\/p>\n
SELECT OBJECT_NAME(sm.object_id)\r\nFROM sys.sql_modules AS sm\r\nWHERE sm.definition LIKE '%v[_]customer%'<\/pre>\n
The second option is to actually declare what the escape character will be using the following syntax:<\/p>\n
SELECT OBJECT_NAME(sm.object_id)\r\nFROM sys.sql_modules AS sm\r\nWHERE sm.definition LIKE '%v|_customer%' ESCAPE '|'<\/pre>\n
Using this syntax tells SQL Server to \"escape\" the character immediately following the declared escape character. The example above will work just as well if the vertical pipe was replaced with a different character, an ampersand for example.<\/p>\n
SELECT OBJECT_NAME(sm.object_id)\r\nFROM sys.sql_modules AS sm\r\nWHERE sm.definition LIKE '%v&_currency%' ESCAPE '&'<\/pre>\n
So if you want to escape from the Bronx or from an _ you've got multiple options.<\/p>\n
 <\/p>\n
Props to the wizards who patrol stackoverflow for the original source of this information<\/a>!<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
There are two different ways to perform a wildcard (aka LIKE) search in SQL Server to find a word with an \"_\" underscore. Both options are outlined at the end of the article after a reference to the cult classic Mystery Science Theater 3000: Escape From The Bronx.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[86,43,3,57],"tags":[105,10,6,9,12,11,173,5],"class_list":["post-457","post","type-post","status-publish","format-standard","hentry","category-dba","category-microsoft-sql-server","category-scripts","category-t-sql","tag-impact-analysis","tag-ms-sql","tag-script","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/457","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=457"}],"version-history":[{"count":7,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/457\/revisions"}],"predecessor-version":[{"id":795,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/457\/revisions\/795"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=457"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=457"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}