Vor- und Nachnamen splitten

[Mackhack]

Hallo DPler,

ich muss aus einer Tabelle Daten nehmen und diese geparst in eine andere Tabelle uebernehmen. In meiner Ausgangstabelle sind Vor- und Nachnamen zusammen in einer Spalte getrennt durch ein Leerzeichen. Diesen Namen muss ich jetzt splitten und in der Zieltabelle unter Firstname und Lastname eintragen. Leider ist mein SQL zu sehr eingerostet und ich bekomme es leider nicht hin diesen Vor/Nachnamen zu splitten. Ich versuchte es mit Substring, LTRIM, RTRIM, aber es wird einfach nichts.

Koennte jemand von euch mir bitte helfen?

Danke. Das ist der aktuelle SQL Code den ich habe und wo ich steckengeblieben bin:

Code:

NSERT INTO Leads (BTN,
               JCN,
               ACCT_NUMBER,
               FIRSTNAME,
               LASTNAME,
               ADDRESS,
               ADDRESS2,
               CITY,
               STATE,
               ZIP,
               Import_Date)
SELECT Daily_Temp.dbo.No_Schedule_042512.[HOME PHONE],
      '5113',
      Daily_Temp.dbo.No_Schedule_042512.[ACCOUNT NUMBER],
      SUBSTRING(Daily_Temp.dbo.No_Schedule_042512.[CUSTOMER NAME], 1, CHARINDEX(' ',

[Bummi]

Du hast es doch schon

Code:

Select RTRIM(SUBSTRING(name,1,CHARINDEX(' ',Name))) as Vorname,
LTRIM(SUBSTRING(name,CHARINDEX(' ',Name),Len(Name))) as Name
from Names

[CCRDude]

Nur dass ein Splitten am ersten Leerzeichen natürlich recht unzuverlässig ist. Was machst Du aus "Hans Hugo Müller", was aus "Maria Antonia vom Schloss", die nach der Hochzeit auch noch zu "Maria Antonia Müller-vom Schloss" wird?

[DeddyH]

Richtig, die ursprüngliche (nicht ordentlich normalisierte) Datenstruktur rächt sich nun, so dass es ohne manuelles Nacharbeiten ohnehin nicht geht. Man kann lediglich versuchen, den Regelfall abzubilden, aber der Datenbestand muss anschließend kontrolliert werden.

[himitsu]

Zitat:

Zitat von DeddyH (Beitrag 1163794)

Man kann lediglich versuchen, den Regelfall abzubilden, aber der Datenbestand muss anschließend kontrolliert werden.

Man könnte noch ein Bool-Feld einfügen, worin die Aufteilfunktion erwähnt, ob sie Aufälligkeiten entdeckt hat, wie z.B. mehr als ein Leerzeichen vorhanden, bzw. mehr als 2 Wörter erkannt.
Oder man loggt die entsprechenden Namen irgenwo mit.

Das würde zumindestens die Suche nach offensichtlichen Problemen beschleunigen.

[Gustav.R]

Zitat:

Zitat von DeddyH (Beitrag 1163794)

Richtig, die ursprüngliche (nicht ordentlich normalisierte) Datenstruktur rächt sich nun, so dass es ohne manuelles Nacharbeiten ohnehin nicht geht. Man kann lediglich versuchen, den Regelfall abzubilden, aber der Datenbestand muss anschließend kontrolliert werden.

Wie wahr, wie wahr :evil:

Beim ersten Leerzeichen (von vorne) zu splitten ist kompletter Unsinn, beim ersten Leerzeichen (von hinten) zu splitten erfordert anschließend etwas/minimal weniger Handarbeit bei der Datenkontrolle :evil:

Und dann gibt es noch die Österreicher, die nicht "Otto Normalverbraucher" heißen, sondern "Normalverbraucher Otto". Vor- und Nachname also andersrum :evil:

Wie DeddyH schon schrub: Es RÄCHT sich!

GG

[Bummi]

Zitat:

schrub

? >> Schriebte !

Ja es rächt sich, aber das Kind ist ja schon in den Brunnen gefallen, wahrscheinlich nicht mal in Ihren eigenen ...

[mkinzler]

Es scheint eher das Nachbarkind in seinen Brunnen gefallen zu sein.

Die Zeit, die man sich beim Design spart, wendet man nunmal x-fach später auf, um das unsaubere Design zu fixen.

[Gustav.R]

Zitat:

Zitat von mkinzler (Beitrag 1163825)

Es scheint eher das Nachbarkind in seinen Brunnen gefallen zu sein.

Die Zeit, die man sich beim Design spart, wendet man nunmal x-fach später auf, um das unsaubere Design zu fixen.

Bei mir war es tatsächlich das Nachbarkind. Habe den Spalteninhalt der kombinierten VorNachnamenspalte dann nach Excel rüberkopiert und mit Excel rumgebastelt (war einfach einfacher als mit Delphi rumzufummeln).

Schrieb, schrub? Schrob :thumb:

Beim Datenbankdesign liebe ich Frau "Sabine Leutheusser-Schnarrenberger" wegen der Feldlängen. Und wünsche Ihrem Ehegatten einen "Prof. h. c." schon wegen der Leerzeichen im Titel :wink:

GG

[Mackhack]

Das Problem ist eben dass wir diese Dateien so von unseren Kunden bekommen und eingelesen werden müssen. Ganz schlimm sind diese hier: Hans * Meier. Kein Mensch weiß warum manche Namen ein * darin haben. Oder Namen wie diesen hier: LAURA * VERARDO GOODRIC

[DeddyH]

Etwas in der Art hatte ich mir schon gedacht, mein Einwand sollte kein Vorwurf an Dich sein.

[Mackhack]

Zitat:

Zitat von DeddyH (Beitrag 1163924)

Etwas in der Art hatte ich mir schon gedacht, mein Einwand sollte kein Vorwurf an Dich sein.

Ok Danke für die Klärung.

[himitsu]

Wenn du Glück hast, dann stellt das * zufällig die Trennung dar, welche du gern hättest.

Die komplett großgeschriebenen Namen könnte man zwar auch anpassen, also alle Wörter mit großgeschriebenem Anfangsbuchstaben und sonst klein.
Was aber natürlich Probleme gibt, wenn eigentlich Anfangsbuchstaben klein sind und nachfolgende Buchstaben doch groß sein sollten.

[Popov]

Wenn die Daten so von dem Kunden kamen, stellt sich die Frage was der Vertrag aussagt. Wer ist für solche Aufsplittung zuständig?

[Mackhack]

Zitat:

Zitat von himitsu (Beitrag 1163936)

Wenn du Glück hast, dann stellt das * zufällig die Trennung dar, welche du gern hättest.

Die komplett großgeschriebenen Namen könnte man zwar auch anpassen, also alle Wörter mit großgeschriebenem Anfangsbuchstaben und sonst klein.
Was aber natürlich Probleme gibt, wenn eigentlich Anfangsbuchstaben klein sind und nachfolgende Buchstaben doch groß sein sollten.

GROSS/Kleinschreibung spielt in diesem Falle keine Rolle. Der * ist leider nicht in jedem Datensatz vorhanden. Nur in manchen. Noch nicht mal der Kunde weiß warum.

Jetzt habe ich aber noch ein Problem: Ein Feld sieht noch so aus "CITY,ST ZIP". Ich habe es geschafft CITY und ZIP zu extrahieren, aber ich scheitere an der ST(ate). Das Pattern sieht so aus:

Code:

LAS VEGAS  NV 89103-5041

Zwischen Stadt und Staat sind immer 2 Leerzeichen, nach dem Staate und vor dem Zip immer ein Leerzeichen. Weiß jemand wie man das ggf. mit LTRIM/RTRIM herausbekommt?

Vielen Dank.

[Bummi]

Code:

Create FUNCTION [dbo].[F_SplitCity](@All Varchar(50)) 
--201204 by Thomas Wassermann
RETURNS 
@tab TABLE 
(
City Varchar(50),
State Varchar(50),
ZIP Varchar(50)
)
AS
BEGIN
   Declare @tmp varchar(50)
   Declare @zip varchar(50)
   Declare @State varchar(50)
    
    Set @tmp =RTRIM(LTRIM(REVERSE(@ALL)))
    SET @zip=RTRIM(SUBSTRING(@tmp,1,CharIndex(' ',@tmp)))
    SET @tmp=LTRIM(RTRIM(REPLACE(@tmp,@zip,'')))
    SET @zip=REVERSE(@Zip)
    Set @State=RTRIM(SUBSTRING(@tmp,1,CharIndex(' ',@tmp)))
    SET @tmp=REPLACE(@tmp,@State,'') -- hier gegf. etwas anderes einbauen als Replace
    SET @State=REVERSE(@State)
    Insert into @tab Select Reverse(@tmp),@State,@Zip
   RETURN 
END

Verwendung

Code:

Select *
from CST Cross Apply [dbo].[F_SplitCity](CST.[ALL])

bei CST = bestehende Adresstabelle und ALL = das Sumpffeld mit City-State-Zip

[Mackhack]

Vielen Dank Bummi.

Es wird immer besser. Gleicher Kunde, anderer Standort gibt uns dieses hier:

Code:

WO #,ACCOUNT #,NAME,Phone,ADDRESS 1,ADDRESS 2,ADDRESS 3,SCHEDULE DATE,TIME,Basic,Digital,PREF_PREM,VALUE
12345678,12345678,Max Mueller,7573183267,631 CONNECTICUT AVE,"NORFOLK, VA 23508-2707",,2012/04/27,A1 ALLDAY,1,0,0,0
60818580,11590213,Mueller Jonny,3369068368,APT 204,7712 ENFIELD AVE,"NORFOLK, VA 23505-1950",2012/04/27,K2 8-10C,0,0,0,0
60817325,13009505,Schuster L Ulli,7579655583,804 S CLUB HOUSE RD,"VIRGINIA BEACH, VA 23452-6402",,2012/04/30,J1 1 - 3,1,0,0,0

Alle Zahlen und Namen sind natürlich frei erfunden.

Wie man sehen kann ist im Address1 mal die Straße mit Hausnummer (korrekt), mal die Apartment Unit was wenn eine vorhanden ist in Address2 stehen sollte, wenn nicht sollte da die Stadt stehen, danach Staat und anschließend der Zip Code. Es ist alles durcheinander. Oder City, State und Zip sind wieder in einer Spalte.

Namen hat jetzt auch wie in der anderen Datei Vor- und Nachname nicht getrennt und ab und zu haben die Leute auch einen Mittel-Initial-Buchstaben.

Vertrag ist gut, wer diese Dateien so nicht haben möchte macht mit diesen Firmen gar kein Geschäft. Es interessiert die leider nicht.

[Bummi]

Tja, klarer Fall von ShiSho, lass vom Kunden definieren wie die Regeln aussehen sollen, mit dem was rauskommt wird er leben, bzw. im Laufe der Zeit manuell korrigieren müssen.

[sx2008]

Du solltest deinen Kunden darauf hinweisen, dass es jede Menge Firmen gibt, deren Hauptprodukt Software für Adresskorrektur ist.
So eine Software kann ohne Weiteres über 50000 Euro kosten, weil das autom. Korrigieren und Normieren von Adressen so kompliziert sein kann.
Wenn du dich darauf einlässt, die Namen zu splitten, dann musst du damit rechnen immer wieder nachbessern zu müssen.
Mach deinem Kunden irgendwie klar, dass er sich nicht eine Leistung durch die Hintertür erschleichen kann, die bei anderen Firmen tausende Euro kosten. (natürlich nicht das Wort "erschleichen" verwenden)

[Furtbichler]

Vollkommen richtig. Lasst euch nach Aufwand bezahlen.

Tipp: Straßen-, Städte-, ZIP-Verzeichnise besorgen und einen heuristisch-automatischen Abgleich vornehmen. Ihr versucht also, Straße, ZIP, State, Stadt zu extrahieren, prüft, ob es passt und schmeißt den Müll in eine Korrekturmappe.

Die wird dann manuell korrigiert.

Erkennt man Gemeinsamkeiten im Müll (fehlende Straße in der Straßenliste?), kann man die Heuristik schrittweise verbessern.

[Sir Rufo]

Dann will ich zum OT auch noch was beitragen:

Es ist immer problematisch, wenn der Vertrieb und Entwicklung/Technik nicht eine Einheit ist.
Denn der Vertrieb verspricht dem Kunden meist zu viel, denn der Vertriebler steht gut da, wenn er einen Abschluss hat :stupid:

Und ein Kunde zeigt auch schon mal gerne die Daten, die "normalerweise" im System zu finden sind (sein sollten).
Die machen aber immer die wenigsten Probleme, taugen also zu einer Aufwandsabschätzung nicht.
Und der Kunde möchte zumeist schin vorher wissen, was das kostet.

Und dann auch noch die Konkurrenzsituation, andere sagen, dass das viel günstiger geht, wenn der Kunde im Vorfeld die realen Kosten kennt.

Fazit: Eigentlich lügen sich alle gegenseitig in die Tasche (und wollen das anscheinend auch nicht anders), aber ist der erste Schritt gemacht, dann ist der Rückzug meist zu teuer.

BTW Ich bin schon dafür dem Kunden die realen Kosten mitzuteilen und sichte idR auch den Datenbestand vorher selber immer auf der Suche nach den Leichen im Keller. Auch auf die Gefahr hin, dass der Kunde abspringt. Der Kunde kehrt idR auch nicht reumütig zurück, denn dann müsste er einen Fehler eingestehen und dafür hat er dann nicht genug Arsch in der Hose. ;)
Aber diese Einstellung muss man sich auch leisten können - und das ist bei einer One-Man-Show erheblich einfacher.

[Bummi]

@Sir Rufo

bei langjährigen Kunden wickeln wir sowas nach Aufwand ab, das gegenseitige Vertrauen besteht dann ja bereits.

[jobo]

Genau, mit Vertrieb hab ich wenig am Hut, aber wenn es um das Thema geht, Datenimport im weitesten Sinne nur nach Aufwand.
Andererseits kann man manchmal auch im Vorfeld schon abklopfen, woher die Daten kommen. Wenn es um automatisierte Schnittstellen geht, ist es meist recht harmlos (die Qualitätsausreißer), Signalworte wie CallCenter oder Excel bedeuten idR das Gegenteil.

[Mackhack]

Zitat:

Zitat von Bummi (Beitrag 1163967)

Tja, klarer Fall von ShiSho, lass vom Kunden definieren wie die Regeln aussehen sollen, mit dem was rauskommt wird er leben, bzw. im Laufe der Zeit manuell korrigieren müssen.

Was ist denn shisho? Shit in shit out oder? :)

[Bummi]

@Mackhack

genau ;-)

[Mackhack]

Dann möchte ich auch nochmal was sagen. Eure OT Gedanken sind Super und ich glaube dass wir hier alle auf der selben Seite stehen. Seit Jahren versuchen wir daran was zu ändern, ohne Erfolg. Leider ist es nicht möglich diesen Kunden das umzugewöhnen. Es kommt so jeden Tag bei uns an mit neuen Datensätzen die wir dann in die DB einpflegen müssen. Denen ist es auch völlig egal wie wir damit arbeiten müssen damit wir die Daten verwenden können. Wenn es uns nicht passt sind wir leider weg, so einfach ist das.

[sx2008]

Zitat:

Zitat von Mackhack (Beitrag 1164064)

Wenn es uns nicht passt sind wir leider weg, so einfach ist das.

Ok, dann heisst das also Kröten schlucken. :(
Dann würde ich empfehlen, das Splitten der Namen (oder ganz allgemein das "Putzen" der Daten) nicht mit SQL vorzunehmen sondern mit Delphi oder Scriptsprachen wie z.B. VBScript oder Javascript.
SQL ist in Punkto Stringverarbeitung einfach zu schwach/umständlich.

[Mackhack]

Und genau so wird es wohl hinauslaufen. Vielen dank nochmal an alle für die Hilfe und die rege Diskussion.