Teile aus vollem Namen ermitteln mit SQL

In diesem Beitrag stelle ich eine Funktion für T-SQL und MySQL vor, um Teile aus einem vollen Namen zu ermitteln. In einem gut geplanten Datenbank-Design sind die Teile des Namens bereits in eigenen Spalten organisiert. Der Name muss dadurch für eine SQL-Abfrage oder Darstellung nicht erst interpretiert werden. Wenn der Name jedoch nur als Ganzes vorhanden ist, kann die hier gezeigte Funktion die Teile des Namens ermitteln.

Die Funktion unterstützt dabei Namen im Format <Vorname> [<Zwischenname>] <Nachname>. Es kann der Vorname und Nachname sowie wenn vorhanden der Zwischenname erkannt werden. Damit lassen sich die Teile oder auch der gesamte Namen in einem neuen Format ermitteln.

Als erster Parameter wird der Funktion der volle Namen im oben gezeigten Format übergeben. Über den zweiten Parameter, welcher das Format der Rückgabe definiert, kann angegeben werden, welche Informationen die Funktion zurückliefern soll. Über die Buchstaben L („Lastname“), F („Firstname“) und M („Middlename“) kann so ein bestimmter Teil oder ein neues Format für die Rückgabe festgelegt werden.

T-SQL (ab SQL-Server 2008)

CREATE FUNCTION GetNamePart(@fullname VARCHAR(200), @format VARCHAR(30))
RETURNS VARCHAR(200)
AS
BEGIN

  -- replace multiple spaces of the fullname and trim the result.
  SET @fullname = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@fullname, ' ', '<>'), '><', ''), '<>', ' ')))

  -- get the different name parts (firstname, middlename and lastname) of the fullname.
  DECLARE @first_name VARCHAR(100)
  SET @first_name = LTRIM(RTRIM(LEFT(@fullname, CHARINDEX(' ', @fullname))))
  DECLARE @last_name VARCHAR(100)
  SET @last_name = LTRIM(RTRIM(RIGHT(@fullname, CHARINDEX(' ', REVERSE(@fullname)))))
  DECLARE @middle_name VARCHAR(100)
  SET @middle_name = LTRIM(RTRIM(SUBSTRING(@fullname, LEN(@first_name) + 1, LEN(@fullname) - LEN(@first_name) - LEN(@last_name))))

  -- init the formatted name of the fullname.
  DECLARE @formatted_name VARCHAR(100)

  -- return only the formatted name if format string is valid.
  IF PATINDEX('%[^LMF]%', UPPER(@format)) > 0
    SET @formatted_name = ''
  ELSE
    BEGIN
      SET @format = REPLACE(REPLACE(REPLACE(@format, 'M', '##M##'), 'L', '##L##'), 'F', '##F##')
      SET @formatted_name = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(UPPER(@format), '##F##', @first_name + ' '), '##M##', @middle_name + ' '), '##L##', @last_name + ' ')))
    END

   -- check the input (@fullname) for valid value (firstname, lastname or firstname, middlename, lastname).
   IF PATINDEX('%_ %_% _%', @fullname) = 0 AND PATINDEX('%_ _%', @fullname) = 0
     SET @formatted_name = ''

   -- return the new formatted name and replace multiple spaces.
  RETURN NULLIF(REPLACE(REPLACE(REPLACE(@formatted_name, ' ', '<>'), '><', ''), '<>', ' '), '')
END

Die über den oben gezeigten Befehl erstellte Funktion kann wie folgt unter T-SQL verwendet werden:

SELECT dbo.GetNamePart('John Tom Doe', 'M');  -- Tom
SELECT dbo.GetNamePart('John Tom Doe', 'LF'); -- Doe John

Weitere Beispiele und Test-Fälle sind in dieser Online-Demo zu finden.


MySQL

CREATE FUNCTION GetNamePart(fullname VARCHAR(200), format VARCHAR(30))
RETURNS VARCHAR(200) DETERMINISTIC NO SQL
BEGIN

  -- set the fullname and format to an internal variable.
  DECLARE strFullname VARCHAR(200) DEFAULT TRIM(REPLACE(REPLACE(REPLACE(fullname, ' ', '<>'), '><', ''), '<>', ' '));
  DECLARE strFormat VARCHAR(30) DEFAULT UPPER(TRIM(format));
  
  -- set the firstname, middlename and lastname to empty string.
  DECLARE strFirstname, strMiddlename, strLastname VARCHAR(200) DEFAULT '';
  
  -- get the firstname, middlename and lastname from fullname.
  SET strFirstname = LEFT(strFullname, LOCATE(' ', strFullname));
  SET strLastname = RIGHT(strFullname, LOCATE(' ', REVERSE(strFullname)));
  SET strMiddlename = SUBSTRING(strFullname, LENGTH(strFirstname) + 1, LENGTH(strFullname) - LENGTH(strFirstname) - LENGTH(strLastname));
  
  -- replace the format chars (L, M, F) as placeholder for later replacement with values.
  SET strFormat = REPLACE(REPLACE(REPLACE(UPPER(format), 'L', '##L##'), 'M', '##M##'), 'F', '##F##');
  
  -- set the values (firstname, middlename, lastname) to the placeholder.
  SET strFormat = TRIM(REPLACE(REPLACE(REPLACE(strFormat, '##L##', CONCAT(strLastname, ' ')), '##M##', CONCAT(strMiddlename, ' ')), '##F##', CONCAT(strFirstname, ' ')));
  
  -- clear the formatted value if the format string is not valid or the fullname is not a valid format.
  SET strFormat = IF(REGEXP_INSTR(UPPER(format), '[^FML]') > 0, '', strFormat);
  SET strFormat = IF(REGEXP_LIKE(strFullname, '.+ .+ .+') = 0 AND REGEXP_LIKE(strFullname, '.+ .+') = 0, '', strFormat);
  
  -- trim and replace multiple spaces on the formatted value.
  SET strFormat = TRIM(REPLACE(REPLACE(REPLACE(strFormat, ' ', '<>'), '><', ''), '<>', ' '));
  
  -- return the formatted value.
  RETURN NULLIF(strFormat, '');
END

Die über den oben gezeigten Befehl erstellte Funktion kann wie folgt unter MySQL verwendet werden:

SELECT GetNamePart('John Tom Doe', 'M');  -- M
SELECT GetNamePart('John Tom Doe', 'LF'); -- Doe John

Weitere Beispiele und Test-Fälle sind in dieser Online-Demo zu finden.

Avatar for Sebastian Brosch

Sebastian Brosch

Ich bin gelernter Fachinformatiker für Anwendungsentwicklung und konnte bereits Erfahrungen in der Entwicklung von Desktop- und Web-Anwendungen sammeln. In diesem Blog schreibe ich über Probleme und Erfahrungen aus dem Gebiet der Anwendungsentwicklung. Ich werde hier meist Artikel aus den Bereichen .NET, Datenbanken (hauptsächlich T-SQL und MySQL), PHP, HTML, CSS und Docker schreiben.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.