Come controllare se una colonna carattere contiene valori numerici<\/p>\n\n\n\n
WITH T_01 (C02, C05, c07)\n AS (VALUES ('12','12345', '1234567'),\n ('1x','12M45', '1234567'),\n ('12','12345', '12K4567'),\n ('1a','12X45', '123N567'),\n ('1d','1c345', '123d567'))\nSELECT \n C02, (CASE WHEN (TRANSLATE(trim(C02), 'NNNNNNNNNNX', '1234567890N')='NN')\n THEN 'is numeric' ELSE 'not numeric' END) C02_NUM,\n C05, (CASE WHEN TRANSLATE (trim(C05), 'NNNNNNNNNNX', '1234567890N')='NNNNN'\n THEN 'is numeric' ELSE 'not numeric' END) C05_NUM,\n C07, (CASE WHEN TRANSLATE (trim(C07), 'NNNNNNNNNNX', '1234567890N')='NNNNNNN'\n THEN 'is numeric' ELSE 'not numeric' END) C07_NUM\n FROM T_01<\/pre>\n\n\n\n<\/p>\n\n\n\n
Result:<\/p>\n\n\n\n
---\nC02 C02_NUM C05 C05_NUM C07 C07_NUM \n12 is numeric 12345 is numeric 1234567 is numeric \n1x not numeric 12M45 not numeric 1234567 is numeric \n12 is numeric 12345 is numeric 12K4567 not numeric\n1a not numeric 12X45 not numeric 123N567 not numeric\n1d not numeric 1c345 not numeric 123d567 not numeric\n---<\/pre>\n","protected":false},"excerpt":{"rendered":"Come controllare se una colonna carattere contiene valori numerici WITH T_01 (C02, C05, c07) AS (VALUES (’12’,’12345′, ‘1234567’), (‘1x’,’12M45′, ‘1234567’), (’12’,’12345′, ’12K4567′), (‘1a’,’12X45′, ‘123N567’), (‘1d’,’1c345′, ‘123d567’)) SELECT C02, (CASE WHEN (TRANSLATE(trim(C02), ‘NNNNNNNNNNX’, ‘1234567890N’)=’NN’) THEN ‘is numeric’ ELSE ‘not numeric’ END)…<\/p>\n","protected":false},"author":3,"featured_media":3992,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_kadence_starter_templates_imported_post":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[15,17],"tags":[29,31,40,46,91],"class_list":["post-3912","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-gestire-as400-con-sql","category-piu-in-profondita","tag-as400","tag-db2","tag-iseries","tag-sql","tag-translate"],"_links":{"self":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/3912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/comments?post=3912"}],"version-history":[{"count":1,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/3912\/revisions"}],"predecessor-version":[{"id":3913,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/3912\/revisions\/3913"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/media\/3992"}],"wp:attachment":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/media?parent=3912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/categories?post=3912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/tags?post=3912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}