{"id":534,"date":"2024-12-27T08:44:12","date_gmt":"2024-12-27T08:44:12","guid":{"rendered":"https:\/\/datadandies.nl\/?p=534"},"modified":"2024-12-27T08:44:12","modified_gmt":"2024-12-27T08:44:12","slug":"search-all-columns-from-a-table-for-a-certain-value-in-snowflake-using-the-search-function","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2024\/12\/27\/search-all-columns-from-a-table-for-a-certain-value-in-snowflake-using-the-search-function\/","title":{"rendered":"Search all columns from a table for a certain value in Snowflake using the SEARCH function"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Searching a certain value in ANY column in a table has never been easier in Snowflake.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine you have an order number, and you would like to search for this order number in a whole bunch of tables that you do not know well.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you knew which column to search, this would be easy:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Simply SELECT * FROM DB.SCHEMA.TABLE WHERE INCONSPICUOUS_COLUMN = \u2018ordernumber\u2019<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However! If only life would be so easy! You do not know which column you need to search!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ideally you would like to search ALL columns in the entire table at once!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is where the function SEARCH in Snowflake comes in handy.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It works like below:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT\n*\nFROM DB.SCHEMA.TABLE\nWHERE SEARCH(DB.SCHEMA.TABLE.*,\t\t-- Using a wildcard (*) in order to search all the columns from TABLE\n\t\t\u2018ordernumber\u2019)\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Kopieer\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> DB.SCHEMA.<\/span><span style=\"color: #81A1C1\">TABLE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">WHERE<\/span><span style=\"color: #D8DEE9FF\"> SEARCH(DB.SCHEMA.<\/span><span style=\"color: #81A1C1\">TABLE<\/span><span style=\"color: #D8DEE9FF\">.<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">,\t\t<\/span><span style=\"color: #616E88\">-- Using a wildcard (*) in order to search all the columns from TABLE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t\t\u2018ordernumber\u2019)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This will search all columns from the table for the order number that you would like to inspect.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">One thing to keep in mind is the following: when you provide the function SEARCH with a list of values like \u2018blue green red\u2019 or \u2018blue-green-red\u2019, the function will start searching for values \u2018blue\u2019, \u2018green\u2019 and \u2018red\u2019. With other words, spaces and dashes (among other characters) are used as delimiters.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you wish to search for an exact match, you can use the optional argument ANALYZER, like below.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT\n*\nFROM DB.SCHEMA.TABLE\nWHERE SEARCH(DB.SCHEMA.TABLE.*,\n\t\t\u2018ordernumber\u2019,\nANALYZER =&gt; \u2018NO_OP_ANALYZER\u2019)\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Kopieer\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> DB.SCHEMA.<\/span><span style=\"color: #81A1C1\">TABLE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">WHERE<\/span><span style=\"color: #D8DEE9FF\"> SEARCH(DB.SCHEMA.<\/span><span style=\"color: #81A1C1\">TABLE<\/span><span style=\"color: #D8DEE9FF\">.<\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t\t\u2018ordernumber\u2019,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">ANALYZER <\/span><span style=\"color: #81A1C1\">=&gt;<\/span><span style=\"color: #D8DEE9FF\"> \u2018NO_OP_ANALYZER\u2019)<\/span><\/span><\/code><\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Searching a certain value in ANY column in a table has never been easier in Snowflake. Imagine you have an order number, and you would like to search for this order number in a whole bunch of tables that you do not know well. If you knew which column to search, this would be easy:&hellip;<\/p>\n<p class=\"more-link\"><a href=\"https:\/\/datadandies.nl\/index.php\/2024\/12\/27\/search-all-columns-from-a-table-for-a-certain-value-in-snowflake-using-the-search-function\/\" class=\"themebutton\">Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[40,4],"class_list":["post-534","post","type-post","status-publish","format-standard","hentry","category-blog","tag-snowflake","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/534","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=534"}],"version-history":[{"count":1,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/534\/revisions"}],"predecessor-version":[{"id":535,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/534\/revisions\/535"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}