Tags:

SQL: Texte mit Umlauten und Sonderzeichen normieren

Von Tobias Arnhold → 2.16.2016
Im heutigen Beispiel möchte ich einmal kurz aufzeigen, wie Sie einen Text nach Ihren Bedürfnissen hin normieren können. Ich habe dazu die bekanntesten Länder der Erde in einer WITH Clause zusammengefasst und bilde mir anhand des Namens einen später verwendbaren normierten String.

Der Zielstring muss GROSSGESCHRIEBEN sein und darf keine Umlaute (ÖÜÄöüäß) und Sonderzeichen (, '.()-) beinhalten.

Die Datenbasis bildet eine Tabelle der APEX Dashboard Competition.

Code: 
create table laender_liste as
select
 distinct
 country
from gdb_world_population
order by country
Jetzt folgt eine Funktion die auf Basis einer Tabelle ein ausführbares WITH-Clause-Select generiert:
SQL Probleme und das Oracle SQL Forum :
Code: 
WITH LAENDER_LISTE AS (
SELECT 
'Afghanistan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Ägypten'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Albanien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Algerien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Andorra'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Angola'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Antigua und Barbuda'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Äquatorialguinea'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Argentinien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Armenien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Aserbaidschan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Äthopien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Australien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Bahamas'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Bahrain'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Bangladesch'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Barbados'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Belgien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Belize'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Benin'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Bhutan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Bolivien, Plurinationaler Staat'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Bosnien und Herzegowina'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Botsuana'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Brasilien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Brunei Darussalam'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Bulgarien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Burkina Faso'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Burundi'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Cabo Verde'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Chile'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'China'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Costa Rica'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Cote d''Ivoire'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Dänemark'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Deutschland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Dominica'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Dominikanische Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Dschibuti'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Ecuador'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'El Salvador'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Eritrea'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Estland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Fidschi'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Finnland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Frankreich'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Gabun'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Gambia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Georgien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Ghana'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Grenada'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Griechenland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Guatemala'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Guinea'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Guinea-Bissau'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Guyana'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Haiti'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Honduras'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Indien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Indonesien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Irak'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Iran, Islamische Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Irland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Island'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Israel'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Italien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Jamaika'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Japan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Jemen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Jordanien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kambodscha'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kamerun'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kanada'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kasachstan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Katar'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kenia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kirgisistan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kiribati'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kolumbien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Komoren'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kongo, Demokratische Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kongo, Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Korea, Demokratische Volksrepublik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Korea, Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kroatien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kuba'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Kuwait'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Laos, Demokratische Volksrepublik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Lesotho'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Lettland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Libanon'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Liberia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Libyen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Liechtenstein'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Litauen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Luxemburg'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Madagaskar'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Malawi'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Malaysia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Malediven'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mali'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Malta'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Marokko'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Marshallinseln'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mauretanien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mauritius'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mazedonien, ehemalige jugoslawische Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mexiko'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mikronesien, Föderierte Staaten von'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Moldau, Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Monaco'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mongolei'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Montenegro (ab 03062006)'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Mosambik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Myanmar'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Namibia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Nauru'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Nepal'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Neuseeland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Nicaragua'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Niederlande'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Niger'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Nigeria'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Norwegen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Oman'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Österreich'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Pakistan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Palau'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Panama'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Papua-Neuguinea'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Paraguay'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Peru'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Philippinen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Polen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Portugal'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Ruanda'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Rumänien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Russische Föderation'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Salomonen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Sambia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Samoa'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'San Marino'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Sao Tome und Principe'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Saudi-Arabien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Schweden'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Schweiz'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Senegal'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Serbien (einschl Kosovo) (03062006-16022008)'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Seychellen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Sierra Leone'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Simbabwe'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Singapur'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Slowakei'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Slowenien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Somalia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Spanien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Sri Lanka'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'St Kitts und Nevis'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'St Lucia'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'St Vincent und die Grenadinen'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Südafrika'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Sudan (einschließlich Südsudan) (bis 08072011)'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Südsudan (ab 09072011)'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Suriname'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Swasiland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Syrien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Tadschikistan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Tansania'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Thailand'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Timor-Leste'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Togo'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Tonga'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Trinidad und Tobago'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Tschad'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Tschechische Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Tunesien'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Türkei'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Turkmenistan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Tuvalu'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Uganda'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Ukraine'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Ungarn'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Uruguay'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Usbekistan'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Vanuatu'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Vatikanstadt'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Venezuela, Bolivarische Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Vereinigte Arabische Emirate'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Vereinigte Staaten'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Vereinigtes Königreich'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Vietnam'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Weißrussland'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Zentralafrikanische Republik'  as COUNTRY FROM DUAL UNION ALL 
SELECT 
'Zypern'  as COUNTRY FROM DUAL
) 
SELECT 
 T1.COUNTRY, 
 UPPER(TRANSLATE ( REGEXP_REPLACE (REGEXP_REPLACE ( REPLACE ( COUNTRY
                        , 'ß'
                        , 'ss'
                        )
                   , '([ÖÜÄöüä])'
                   , '\1e'
                   )
              , '\)\ \(|\ \(|\, |\ |,|\.|\(|\)|\''|\-'
              , '_'
              )
      , 'ÖÜÄöüä'
      , 'OUAoua'
 )) AS TRANSFORMED_COUNTRY 
FROM LAENDER_LISTE T1 
ORDER BY 1
Mit Hilfe von REGEXP_REPLACE und TRANSLATE (Danke Oracle Forum) konnte ich nun einen genormten Namen generieren.

Das Ergebnis:

Post Tags:

Veröffentlicht von Tobias Arnhold Am 00:01
Kategorien: