{"id":401,"date":"2011-04-28T08:47:49","date_gmt":"2011-04-28T06:47:49","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=401"},"modified":"2011-06-07T11:51:04","modified_gmt":"2011-06-07T09:51:04","slug":"ms-sql-server-numerische-fuhrende-nullen-in-material","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=401","title":{"rendered":"MS SQL Server und ACCESS: F\u00fchrende Nullen in numerischem String abschneiden"},"content":{"rendered":"<h1>Aufgabenstellung<\/h1>\n<p>In einer Spalte mit Materialnummer\/Artikelnummern werden bei allen numerischen Materialnummern die Nullen entfernt, alphanumerische Materialnummern bleiben unber\u00fchrt. Soll auch bei den  alphanumerischen Materialnummern die f\u00fchrenden Nullen entfernt werden, verweise ich auf den Artikel in der Rubrik &#8222;<a href=\"https:\/\/www.capri-soft.de\/blog\/?p=271\">MS SQL Server: F\u00fchrende Nullen bei alphanumerischem String&#8220;<\/a>.<\/p>\n<h1>Ansatz<\/h1>\n<p>\u00dcber das Case Statement wird gefragt ob der Artikel numerisch ist. Ist er numerisch, wird \u00fcber die Konvertierung in einen Integer (und anschlie\u00dfendes Zur\u00fcckkonvertieren in VARCHAR)  die f\u00fchrenden Nullen entfernen.<\/p>\n<h1>L\u00f6sung<\/h1>\n<p>Microsoft SQL Server:<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\nUPDATE tabelle SET material=\r\nCASE WHEN isnumeric(material)=1 \r\n        THEN convert(VARCHAR, convert(INTEGER, material))\r\n        ELSE convert(VARCHAR, material)\r\nEND \r\n<\/pre>\n<p>Microsoft Access:<\/p>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n   UPDATE articletexts SET material=\r\n   IIF(IsNumeric(material), \r\n   REPLACE(LTRIM(REPLACE(material,&#039;0&#039;,&#039; &#039;))\r\n   ,&#039; &#039;,&#039;0&#039;), material) \r\n<\/pre>\n<iframe src=\"http:\/\/www.facebook.com\/plugins\/like.php?href=https%3A%2F%2Fwww.capri-soft.de%2Fblog%2F%3Fp%3D401&amp;layout=standard&amp;show_faces=true&amp;width=450&amp;action=like&amp;colorscheme=light\" scrolling=\"no\" frameborder=\"0\" allowTransparency=\"true\" style=\"border:none; overflow:hidden; width:450px;margin-top:5px;\"><\/iframe>","protected":false},"excerpt":{"rendered":"<p>Aufgabenstellung In einer Spalte mit Materialnummer\/Artikelnummern werden bei allen numerischen Materialnummern die Nullen entfernt, alphanumerische Materialnummern bleiben unber\u00fchrt. Soll auch bei den alphanumerischen Materialnummern die f\u00fchrenden Nullen entfernt werden, verweise ich auf den Artikel in der Rubrik &#8222;MS SQL Server: F\u00fchrende Nullen bei alphanumerischem String&#8220;. Ansatz \u00dcber das Case Statement wird gefragt ob der Artikel &hellip; <a href=\"https:\/\/www.capri-soft.de\/blog\/?p=401\" class=\"more-link\"><span class=\"screen-reader-text\">MS SQL Server und ACCESS: F\u00fchrende Nullen in numerischem String abschneiden<\/span> weiterlesen <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[16,11],"tags":[],"class_list":["post-401","post","type-post","status-publish","format-standard","hentry","category-access","category-ms-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4yGeN-6t","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=401"}],"version-history":[{"count":7,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/401\/revisions"}],"predecessor-version":[{"id":422,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/401\/revisions\/422"}],"wp:attachment":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}