Parsing Conflated Names into Surname and Forename in SQLite

Understanding the Challenge of Splitting Combined Name Fields

Issue Overview: Ambiguous Name Structure and Parsing Complexity

The core challenge revolves around deconstructing a single string field containing combined surnames and forenames into two distinct fields. The input data follows inconsistent formatting rules where surnames precede forenames, but both components may contain multiple words, hyphens, initials, or special characters. For example:

• Washington John Adams George K should split into Surname: Washington John Adams and Forename: George K
• Brook - Hampster Gervaise becomes Surname: Brook - Hampster and Forename: Gervaise

This problem is inherently ambiguous because:

1. Variable-Length Components: The surname and forename segments have no fixed word count.
2. Delimiter Ambiguity: Spaces and hyphens serve dual roles—separating name components and acting as part of surnames/forenames (e.g., St John Mollusc Oliver vs. On T V).
3. Edge Cases: Hyphenated names (Smith-Smythe-Smith), trailing initials (George K), and multi-word surnames (Norwegian Blue Parrot) complicate pattern detection.

Traditional string-splitting methods (e.g., splitting at the first or last space) fail because they cannot dynamically identify the "split point" between surname and forename. The lack of a consistent delimiter or positional marker necessitates a heuristic approach that analyzes the structure of each name.

Root Causes: Data Modeling Flaws and Parsing Limitations

The root issue stems from violating the First Normal Form (1NF), where atomicity of data is not preserved. Storing multi-component data in a single field creates parsing challenges. Specific technical hurdles include:

1. Non-Deterministic Token Boundaries
   Spaces separate tokens, but their role depends on context. For instance:

   • In Irwin Washington A, the split occurs between Irwin (surname) and Washington A (forename).
   • In Dead L F, Dead is the surname, and L F are initials.

   A naive split on spaces would incorrectly fragment these components.

2. Irregular Use of Hyphens and Special Characters
   Hyphens appear in surnames (Smith-Smythe-Smith) and forenames (Brook - Hampster), preventing the use of simple pattern exclusion. The - substring (with spaces) further complicates tokenization.

3. Dynamic Positional Logic
   The split point between surname and forename varies per record. It cannot be determined via fixed offsets (e.g., "split after the third word") because the number of surname tokens is unpredictable.

4. SQLite’s Limited Native String Functions
   While SQLite provides SUBSTR, INSTR, and TRIM, it lacks built-in functions for:

   • Splitting strings into arrays/rows (e.g., SPLIT_PART in PostgreSQL).
   • Advanced pattern matching (e.g., lookaheads in regular expressions).

   Workarounds involve combining JSON functions (json_each), recursive CTEs, or window functions, which add complexity.

Comprehensive Solutions: Advanced Tokenization and Heuristic Analysis

Below are step-by-step methods to resolve the issue, ordered by increasing complexity and robustness.

Method 1: Window Functions and Sliding Token Analysis

The initial solution uses generate_series and window functions to identify tokens that belong to the forename. Key steps:

1. Tokenize the Input String
   Wrap the name with spaces and split it into tokens bounded by spaces:

   SELECT SubStr(' ' || t.name || ' ', s1.value, s2.value - s1.value - 1) AS part
   FROM t
   JOIN generate_series(1, Length(t.name) + 2) s1
   JOIN generate_series(1, Length(t.name) + 2) s2
   WHERE s1.value < s2.value
     AND SubStr(' ' || t.name || ' ', s1.value, 1) = ' '
     AND SubStr(' ' || t.name || ' ', s2.value, 1) = ' '
   

   This generates all possible substrings between spaces (tokens).

2. Identify Forename Tokens
   Use a window function to check if subsequent tokens contain spaces (indicating they belong to the surname):

   AND part NOT LIKE '% %'
   

   This filters out multi-word tokens, assuming forenames are single words or initials without embedded spaces.

3. Separate Surname and Forename
   Aggregate tokens using GROUP_CONCAT, marking the first valid forename token as the split point:

   SELECT group_concat(CASE WHEN first THEN part END, ' ') Forename,
          group_concat(CASE WHEN NOT first THEN part END, ' ') Surname
   FROM x
   GROUP BY id;
   

Pros: Handles most cases where forenames are single tokens or initials.
Cons: Fails on multi-word forenames (e.g., Mary Ann) if they contain spaces.

Method 2: JSON-Based Tokenization with Positional Heuristics

A more concise approach uses json_each to split the name into tokens and heuristically determine the split point:

SELECT group_concat(IIf(l, NULL, p), ' ') Forename,
       group_concat(IIf(l, p, NULL), ' ') Surname
FROM (
  SELECT t.oid,
         value AS p,
         Max(Length(value) - 1) OVER (
           PARTITION BY t.oid 
           ROWS BETWEEN 1 FOLLOWING AND 9 FOLLOWING
         ) AS l
  FROM t, json_each('["' || Replace(name, ' ', '","') || '"]')
)
GROUP BY oid;

1. JSON Tokenization
   Convert the name into a JSON array using spaces as delimiters:

   json_each('["' || Replace(name, ' ', '","') || '"]')
   

   This splits the name into individual tokens, ignoring embedded spaces.

2. Heuristic Split Point Detection
   The MAX(LENGTH(value) - 1) window function checks if subsequent tokens are likely initials (length ≤ 2). If so, they are assigned to the forename.

Pros: Simpler syntax and handles trailing initials.
Cons: Incorrectly escapes names with quotes and fails on hyphenated surnames.

Method 3: Custom Split Function with Type Inference

For maximum flexibility, implement a custom table-valued function (TVF) to split strings:

CREATE VIRTUAL TABLE temp.split USING statement((
  WITH RECURSIVE input(data, sep) AS (VALUES (:data, coalesce(:sep, ','))),
  tokens(token, data, sep, seplen, pos, isValid) AS (
    SELECT null, data, sep, length(sep), instr(data, sep), false
    FROM input
    UNION ALL
    SELECT substr(data, 1, pos - 1),
           substr(data, pos + seplen), sep, seplen, -1, true
    FROM tokens WHERE pos > 0
    UNION ALL
    SELECT null, data, sep, seplen, instr(data, sep), false
    FROM tokens WHERE pos < 0
    UNION ALL
    SELECT data, null, sep, seplen, null, true
    FROM tokens WHERE pos == 0
  )
  SELECT ToBestType(token) AS value FROM tokens WHERE isValid
));

Usage:

SELECT group_concat(IIf(l, NULL, p), ' ') Forename,
       group_concat(IIf(l, p, NULL), ' ') Surname
FROM (
  SELECT t.oid, value AS p,
         Max(Length(value) - 1) OVER (
           PARTITION BY t.oid 
           ROWS BETWEEN 1 FOLLOWING AND 9 FOLLOWING
         ) AS l
  FROM t CROSS JOIN split(name, ' ')
)
GROUP BY oid;

Pros: Handles custom delimiters and edge cases with proper escaping.
Cons: Requires additional setup and extensions (ToBestType).

Final Recommendations

1. Preprocess Hyphens and Special Characters
   Normalize hyphens using REPLACE to avoid delimiter confusion:

   REPLACE(name, ' - ', '~')  -- Temporary placeholder
   

2. Combine Heuristics for Robustness
   Use a hybrid approach:

   • Split tokens using generate_series or a custom TVF.
   • Apply regex to identify initials (^[A-Z]$).
   • Use window functions to find the first/last token matching the initial pattern.

3. Validation and Edge Case Testing
   Test solutions against a comprehensive dataset, including:

   • Multi-word surnames (Van der Sar).
   • Forenames with hyphens (Jean-Luc).
   • Names with numerals (Henry VIII).

By combining these strategies, you can achieve a robust solution that adapts to the variability inherent in conflated name fields.