John R. Cui johnrcui

Name Splitter

This uses the list of known family name prefixes to identify the correct family name in order to split a person's full name into individual parts. Note that this does not attempt to deal with every honorifics and post-nominal letters. This however supports more common honorific (ie. Mr., Ms., Mrs., etc.) and suffixes (ie. Jr., Sr., I, II, III, etc.).

This is useful for decomposing names entered into a single field before storing the data or for converting existing data that uses full names into name parts.

Regular Expression

The following regex splits the name into 8 identifiable parts:

	DELIMITER //

	-- Compute the Levenshtein distance between two strings within a given max distance
	-- If any point it becomes certain max distance threshold will be crossed, the function
	-- short circuits and returns immedately with a value of `max_distance + 1`
	--
	-- NOTE: This code was generated and optimized using DeepSeek R1
	--
	CREATE FUNCTION MAX_LEVENSHTEIN(s1 VARCHAR(255), s2 VARCHAR(255), max_distance INT) RETURNS INT
	BEGIN

	-- Inspired by MySQL implementation of ULID by
	-- https://gist.github.com/kenji4569/47ce8bbd6bef7b85ba1f97e018f34cf3

	-- Add pgcrypto extension
	CREATE EXTENSION IF NOT EXISTS "pgcrypto";

	-- Decodes a valid ulid (26 digits) into its 128-bit form
	-- encoded as 32 character hex
	CREATE OR REPLACE FUNCTION ULID_DECODE(s CHAR(26))
	RETURNS CHAR(32)

	DELIMITER $$
	DROP FUNCTION IF EXISTS hex_or_hex;

	CREATE FUNCTION IF NOT EXISTS hex_or_hex (
	h1 TEXT,
	h2 TEXT
	)
	RETURNS TEXT
	COMMENT 'Perform a bitwise OR operation between two hex values'
	DETERMINISTIC

	DELIMITER $$

	DROP FUNCTION IF EXISTS rfc6901_to_json_path$$

	-- Convert a JSON Pointer (RFC6901) into a MySQL JSON path
	-- @param rfc6901_path object path in RFC6901 format
	-- @returns path as an array of text
	CREATE FUNCTION rfc6901_to_json_path(
	rfc6901_path TEXT
	)

	-- Convert a JSON Pointer (RFC6901) into an array of text
	-- @param rfc6901_path object path in RFC6901 format
	-- @returns path as an array of text
	CREATE OR REPLACE FUNCTION rfc6901_to_json_path(rfc6901_path TEXT)
	RETURNS TEXT[]
	LANGUAGE plpgsql
	IMMUTABLE
	STRICT
	PARALLEL
	SAFE AS

	export const RE_EMOJI = /[\p{So}\p{Sk}\p{S}\p{M}]/gu;

	export const isEmoji = (str: string) => RegExp('^' + RE_EMOJI.source + '$', 'gu').test(str);

	export const stripEmoji = (str: string) => str.replace(RE_EMOJI, '');