{"id":1311,"date":"2021-03-13T14:38:10","date_gmt":"2021-03-13T14:38:10","guid":{"rendered":"http:\/\/flace.de\/?p=1311"},"modified":"2021-03-13T14:52:27","modified_gmt":"2021-03-13T14:52:27","slug":"sql-aus-der-strasse-string-zeichenkette-die-hausnummer-separieren","status":"publish","type":"post","link":"https:\/\/flace.de\/?p=1311","title":{"rendered":"SQL aus der Strasse (String) Zeichenkette die Hausnummer separieren"},"content":{"rendered":"<p>pattern: Ein Zeichenausdruck, der die zu suchende Sequenz enth\u00e4lt<\/p>\n<p>Mit diesem Beispielscript wird die Strasse in zwei Datenfelder Strasse | Hausnummer separiert.<\/p>\n<p><!--more--><br \/>\n<span style=\"color: #3366ff;\">select strasse,<\/span><br \/>\n<span style=\"color: #3366ff;\">substring(ad.strasse,0,PATINDEX(&#8218;%[0-9]%&#8216;,AD.Strasse)) AS Strasse1,<\/span><br \/>\n<span style=\"color: #3366ff;\">substring(ad.strasse,PATINDEX(&#8218;%[0-9]%&#8216;,AD.Strasse),LEN(ad.strasse)-PATINDEX(&#8218;%[0-9]%&#8216;,AD.Strasse)+1) AS Hausnummer<\/span><br \/>\n<span style=\"color: #3366ff;\">from adradressen as ad<\/span><\/p>\n<p><span class=\"hljs-keyword\">Simples Beispiel<br \/>\n<span style=\"color: #3366ff;\">SELECT<\/span><\/span><span style=\"color: #3366ff;\"> <span class=\"hljs-keyword\">position<\/span> = <span class=\"hljs-keyword\">PATINDEX<\/span>(<span class=\"hljs-string\">&#8218;%<span style=\"color: #ff0000;\">ter<\/span>%&#8216;<\/span>, <span class=\"hljs-string\">&#8218;interesting data&#8216;<\/span>);<br \/>\n<span style=\"color: #000000;\">Ergebnis: Die gesuchte Zeichenkette beginnt ab Position <strong><span style=\"color: #ff0000;\">3<\/span><\/strong><\/span><strong><span style=\"color: #ff0000;\">&nbsp;<\/span><\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>pattern: Ein Zeichenausdruck, der die zu suchende Sequenz enth\u00e4lt Mit diesem Beispielscript wird die Strasse in zwei Datenfelder Strasse | Hausnummer separiert.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[204,16],"tags":[335,334],"class_list":["post-1311","post","type-post","status-publish","format-standard","hentry","category-adressverwaltung","category-sql","tag-patindex","tag-pattern"],"_links":{"self":[{"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts\/1311","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1311"}],"version-history":[{"count":5,"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts\/1311\/revisions"}],"predecessor-version":[{"id":1316,"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts\/1311\/revisions\/1316"}],"wp:attachment":[{"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}