{"id":77,"date":"2011-02-19T17:32:28","date_gmt":"2011-02-19T15:32:28","guid":{"rendered":"http:\/\/lwibs01.gm.fh-koeln.de\/blogs\/sqlexpert\/?p=77"},"modified":"2011-02-22T11:45:34","modified_gmt":"2011-02-22T09:45:34","slug":"select-abfragen-mit-regularen-ausdrucken","status":"publish","type":"post","link":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/2011\/02\/19\/select-abfragen-mit-regularen-ausdrucken\/","title":{"rendered":"SELECT-Abfragen mit regul\u00e4ren Ausdr\u00fccken"},"content":{"rendered":"

findet man hier:
\nSELECT\u00a0\u00a0 REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[A-Za-z]+‘) Adress FROM DUAL;<\/p>\n

SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[[:digit:]]+‘) Adress FROM DUAL;<\/p>\n

SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[e]$‘) Adress FROM DUAL;<\/p>\n

SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚(101)202-3300‘, ‚\\([[:digit:]]{3}\\)‘) telefonnummer\u00a0 FROM DUAL;<\/p>\n

SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚(101)202-3300‘, ‚\\([[:digit:]]{3}\\)[[:digit:]]{3}‘) telefonnummer\u00a0 FROM DUAL;<\/p>\n

SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚(101)202-3300‘, ‚\\([[:digit:]]{3}\\)[[:digit:]]{3}-[[:digit:]]{4}‘) telefonnummer\u00a0 FROM DUAL;<\/p>\n

SELECT\u00a0\u00a0 Nachname, REGEXP_SUBSTR (Nachname, ‚(a|r|e)$‘) AS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚^[[:alpha:] ]+‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]+‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]{8}‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚^(Pa)‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚(er)$‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[a-z]+a‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]+e‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

—Frage: {} funktionieren nicht…, Namen mit genau einem e<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚[[:alpha:] ]+e {2,}‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

—Frage: {} funktionieren nicht…, Namen mit mehr als 2 e’s<\/p>\n

SELECT\u00a0\u00a0 Vorname,
\nNachname,
\nREGEXP_SUBSTR (Vorname || ‚ ‚ || Nachname, ‚e‘)
\nAS testen
\nFROM\u00a0\u00a0 angestellte;<\/p>\n

SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚(101)202-3300‘, ‚[1-3]{3}‘) telefonnummer\u00a0 FROM DUAL;<\/p>\n

SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚Weber‘, ‚e.e*e‘) test\u00a0 FROM DUAL;<\/p>\n

SELECT nachname, REGEXP_SUBSTR (nachname, ‚[^M\u00fc.r$]‘) FROM angestellte order by nachname;<\/p>\n

SELECT nachname, REGEXP_SUBSTR (nachname, ‚[r$]‘) FROM angestellte order by nachname;
\n— alle Angestellten, deren Nachname mit r aufh\u00f6rt.<\/p>\n

SELECT nachname, REGEXP_SUBSTR (nachname, ‚[B^].+‘) FROM angestellte order by nachname;
\n— alle Angestellten, deren Nachname mit B anf\u00e4ngt.<\/p>\n","protected":false},"excerpt":{"rendered":"

findet man hier: SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[A-Za-z]+‘) Adress FROM DUAL; SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[[:digit:]]+‘) Adress FROM DUAL; SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚123 Maple Avenue‘, ‚[e]$‘) Adress FROM DUAL; SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚(101)202-3300‘, ‚\\([[:digit:]]{3}\\)‘) telefonnummer\u00a0 FROM DUAL; SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚(101)202-3300‘, ‚\\([[:digit:]]{3}\\)[[:digit:]]{3}‘) telefonnummer\u00a0 FROM DUAL; SELECT\u00a0\u00a0 REGEXP_SUBSTR (‚(101)202-3300‘, ‚\\([[:digit:]]{3}\\)[[:digit:]]{3}-[[:digit:]]{4}‘) telefonnummer\u00a0 FROM DUAL; SELECT\u00a0\u00a0 Nachname, REGEXP_SUBSTR (Nachname,… <\/a><\/p>\n","protected":false},"author":23,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[260],"tags":[275,274,273],"class_list":["post-77","post","type-post","status-publish","format-standard","hentry","category-17-regular-expressions","tag-regexp_substr","tag-regularer-ausdruck","tag-select"],"acf":[],"_links":{"self":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts\/77","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/users\/23"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/comments?post=77"}],"version-history":[{"count":6,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts\/77\/revisions"}],"predecessor-version":[{"id":400,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts\/77\/revisions\/400"}],"wp:attachment":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/media?parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/categories?post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/tags?post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}