{"id":585,"date":"2013-06-05T06:49:49","date_gmt":"2013-06-05T05:49:49","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=585"},"modified":"2013-06-05T06:50:51","modified_gmt":"2013-06-05T05:50:51","slug":"finding-wildcards","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=585","title":{"rendered":"Finding wildcards"},"content":{"rendered":"<style type=\"text\/css\">\n.code, .code pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #ff0000; }\n.code .kwrd { color: #008080; }\n.code .str { color: #0000ff; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt\n{\n  background-color: #f4f4f4;\n  width: 100%;\n  margin: 0em;\n}\n.code .lnum { color: #606060; }\n<\/style>\n<p>We sometimes use wildcard characters in the values of our columns. This makes it hard to find just these records. When looking for these records you want to search for the wildcard character, but this will return the other records as well.<!--more--><br \/>\nI have created a simple table like this<\/p>\n<div class=\"code\" align=\"left\">\n<pre>\r\n<span class=\"kwrd\">create table<\/span> hardtofind (\r\ntext <span class=\"kwrd\">varchar2<\/span>(<span class=\"str\">100<\/span>)\r\n);\r\n<\/pre>\n<\/div>\n<p>And I have inserted a couple of rows:<\/p>\n<div class=\"code\" align=\"left\">\n<pre>\r\n<span class=\"kwrd\">insert into<\/span> hardtofind(text) <span class=\"kwrd\">values<\/span> ('<span class=\"str\">can easily be found<\/span>');\r\n<span class=\"kwrd\">insert into<\/span> hardtofind(text) <span class=\"kwrd\">values<\/span> ('<span class=\"str\">hard_to_find<\/span>');\r\n<span class=\"kwrd\">insert into<\/span> hardtofind(text) <span class=\"kwrd\">values<\/span> ('<span class=\"str\">also%hard%to%find<\/span>');\r\n<\/pre>\n<\/div>\n<p>If I would like to find the record containing the underscore character (&#8216;hard_to_find&#8217;) my first idea would be to just search for that character in the column. Something like:<\/p>\n<div class=\"code\" align=\"left\">\n<pre>\r\n<span class=\"kwrd\">select<\/span> * <span class=\"kwrd\">from<\/span> hardtofind\r\n<span class=\"kwrd\">where<\/span> 1=1\r\n  <span class=\"kwrd\">and<\/span> text <span class=\"kwrd\">like<\/span> '<span class=\"str\">%_%<\/span>';\r\n<\/pre>\n<\/div>\n<p>This will return all the rows in the table:<\/p>\n<div class=\"code\" align=\"left\">\n<pre>\r\nTEXT\r\n----------------------\r\ncan easily be found\r\nhard_to_find\r\nalso%hard%to%find\r\n<\/pre>\n<\/div>\n<p>How can I search for wildcard characters without using them as a wildcard. One idea is to use the <A HREF='http:\/\/docs.oracle.com\/cd\/B28359_01\/olap.111\/b28126\/dml_functions_1103.htm' target=\"_blank\"><STRONG>INSTR<\/STRONG><\/A> function to check for the existence of this character:<\/p>\n<div class=\"code\" align=\"left\">\n<pre>\r\n<span class=\"kwrd\">select<\/span> * <span class=\"kwrd\">from<\/span> hardtofind\r\n<span class=\"kwrd\">where<\/span> 1=1\r\n  <span class=\"kwrd\">and<\/span> <span class=\"kwrd\">instr<\/span>(text, '<span class=\"str\">_<\/span>') > 0;\r\n<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre>\r\nTEXT\r\n----------------------\r\nhard_to_find\r\n<\/pre>\n<\/div>\n<p>But ofcourse the SQL language provides us with a way to escape the special characters. All you have to do is tell the engine when you want to escape the character:<\/p>\n<div class=\"code\" align=\"left\">\n<pre>\r\n<span class=\"kwrd\">select<\/span> * <span class=\"kwrd\">from<\/span> hardtofind\r\n<span class=\"kwrd\">where<\/span> 1=1\r\n  <span class=\"kwrd\">and<\/span> text <span class=\"kwrd\">like<\/span> '<span class=\"str\">%\\_%<\/span>' <span class=\"kwrd\">escape<\/span> '<span class=\"str\">\\<\/span>';\r\n<\/pre>\n<\/div>\n<p>I used the version with the <STRONG>INSTR<\/STRONG> function but I figured SQL should provide us with means to escape special characters. Doing a little searching led me to this.<br \/>\nAs always, there&#8217;s multiple ways to get to the same result.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We sometimes use wildcard characters in the values of our columns. This makes it hard to find just these records. When looking for these records you want to search for the wildcard character, but this will return the other records as well.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-585","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/585","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=585"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/585\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=585"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=585"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=585"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}