{"id":67,"date":"2009-02-04T12:28:23","date_gmt":"2009-02-04T11:28:23","guid":{"rendered":"http:\/\/unckel.com\/blog\/?p=67"},"modified":"2019-12-07T22:30:49","modified_gmt":"2019-12-07T21:30:49","slug":"sql-telefonnummer-umformatieren-mit-t-sql-replace","status":"publish","type":"post","link":"https:\/\/unckel.de\/blog\/sql-telefonnummer-umformatieren-mit-t-sql-replace\/","title":{"rendered":"SQL: Telefonnummer umformatieren mit T-SQL Replace"},"content":{"rendered":"<p>Telefonnummern in einer SQL-Server-Datenbank ganz einfach mit T-SQL\u00a0<a href=\"http:\/\/de.wikipedia.org\/wiki\/Rufnummer#Schreibweisen\" target=\"_blank\" rel=\"noopener\">nach DIN 5008 umformatieren<\/a>. Daher die bestehende Formatierung anpassen.<\/p>\n<p><b>Ist:<\/b><br \/>\n089\/12345-67<br \/>\n089 \/ 12345-67<br \/>\n(089) 1 23 45-67<\/p>\n<p><b>Soll (nach DIN 5008 ohne L\u00e4ndercode):<\/b><br \/>\n089 12345-67<\/p>\n<pre>-- 1. alle Leerzeichen l\u00f6schen\nUPDATE Users\nSET Phone = REPLACE(Phone,' ','');\n\n-- 2. ge\u00f6ffnete Klammer l\u00f6schen\nUPDATE Users\nSET Phone = REPLACE(Phone,'(','');\n\n-- 3. geschlossene Klammer mit Leerzeichem ersetzen\nUPDATE Users\nSET Phone = REPLACE(Phone,')',' ');\n\n-- 4. Schr\u00e4gstrich mit Leerzeichem ersetzen\nUPDATE Users\nSET Phone = REPLACE(Phone,'\/',' ');<\/pre>\n<p>Quelle:\u00a0<a href=\"http:\/\/blog.schelian.de\/2007\/09\/06\/TSQLReplace.aspx\" target=\"_blank\" rel=\"noopener\">http:\/\/blog.schelian.de\/2007\/09\/06\/TSQLReplace.aspx<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Telefonnummern in einer SQL-Server-Datenbank ganz einfach mit T-SQL\u00a0nach DIN 5008 umformatieren. Daher die bestehende Formatierung anpassen. Ist: 089\/12345-67 089 \/ 12345-67 (089) 1 23 45-67 Soll (nach DIN 5008 ohne L\u00e4ndercode): 089 12345-67 &#8212; 1. alle Leerzeichen l\u00f6schen UPDATE Users SET Phone = REPLACE(Phone,&#8216; &#8218;,&#8220;); &#8212; 2. ge\u00f6ffnete Klammer l\u00f6schen UPDATE Users SET Phone = [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[],"class_list":["post-67","post","type-post","status-publish","format-standard","hentry","category-webdesign"],"_links":{"self":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts\/67","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/comments?post=67"}],"version-history":[{"count":1,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts\/67\/revisions"}],"predecessor-version":[{"id":842,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts\/67\/revisions\/842"}],"wp:attachment":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/media?parent=67"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/categories?post=67"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/tags?post=67"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}