{"id":317,"date":"2014-06-03T17:26:14","date_gmt":"2014-06-03T17:26:14","guid":{"rendered":"http:\/\/localhost\/__sites\/3d_blog\/?p=317"},"modified":"2014-06-18T11:13:36","modified_gmt":"2014-06-18T11:13:36","slug":"select-data-by-non-numeric-characters","status":"publish","type":"post","link":"https:\/\/blog.silverterra.net\/?p=317","title":{"rendered":"select all rows where we have non numeric characters in the column"},"content":{"rendered":"<blockquote><p>Goal: to find all the rows with invalid order number.<\/p><\/blockquote>\n<p>We have a process where some outsider application send orders to a web page and read the answer from the server.<br \/>\nThe answer sometimes is incorrect. The page structure is seemingly good, but we have incorrect data. For example the place of order number contains some other information or some strange text.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"wp-content\/uploads\/2014\/06\/Clipboard01-300x156.jpg\" alt=\"\" width=\"300\" height=\"156\" class=\"alignnone size-medium wp-image-320\" srcset=\"https:\/\/blog.silverterra.net\/wp-content\/uploads\/2014\/06\/Clipboard01-300x156.jpg 300w, https:\/\/blog.silverterra.net\/wp-content\/uploads\/2014\/06\/Clipboard01.jpg 448w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/p>\n<p>So I want to select all the rows where the order number (VARCHAR2) contains non numeric characters.<br \/>\nHere is a simple solution (let&#8217;s suppose we have only alphabetical characters):<\/p>\n<p><strong>1. simple solution<\/strong> (check only we have alphabeticals)<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\nselect *\r\nfrom TA_KR_TRANSPORT a\r\nwhere upper(web_rend) != lower(web_rend)\r\n<\/pre>\n<p>the result shows all the bad records<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"wp-content\/uploads\/2014\/06\/Clipboard02-300x115.jpg\" alt=\"\" width=\"300\" height=\"115\" class=\"alignnone size-medium wp-image-322\" srcset=\"https:\/\/blog.silverterra.net\/wp-content\/uploads\/2014\/06\/Clipboard02-300x115.jpg 300w, https:\/\/blog.silverterra.net\/wp-content\/uploads\/2014\/06\/Clipboard02.jpg 468w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/p>\n<p><strong>2. scientific solution<\/strong> (drop the numbers and check anything else)<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\nselect *\r\nfrom TA_KR_TRANSPORT a\r\nwhere length(replace(translate(web_rend, '0123456789', '0000000000'), '0', '' )) &gt; 0\r\n<\/pre>\n<p>of course the result is the same (the running time is similar like before)<\/p>\n<p>Done. We can fix the receiver code now to try to avoid the similar situations in the future.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Goal: to find all the rows with invalid order number. We have a process where some outsider application send orders to a web page and read the answer from the server. The answer sometimes is incorrect. The page structure is seemingly good, but we have incorrect data. For example the place of order number contains [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-317","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/317","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=317"}],"version-history":[{"count":14,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/317\/revisions"}],"predecessor-version":[{"id":652,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/317\/revisions\/652"}],"wp:attachment":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=317"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}