aonestar.public

Parameters

Name	Type	Mode
p_surname	text	IN
p_given_name	text	IN
out_first_name	text	OUT
out_last_name	text	OUT
out_middle_name	text	OUT

Definition

DECLARE
    v_space_pos integer;
    v_is_force_uppercase boolean := true;
    v_temp_name text;
BEGIN
    -- Initialize outputs to empty string
    out_first_name := '';
    out_last_name := '';
    out_middle_name := '';

-- If both parameters are null or empty, return empty strings
    IF (p_surname IS NULL OR p_surname = '') AND (p_given_name IS NULL OR p_given_name = '') THEN
        RETURN;
    END IF;

-- Case 1 & 3: Only p_given_name is provided
    IF (p_surname IS NULL OR p_surname = '') AND p_given_name IS NOT NULL AND p_given_name != '' THEN
        v_space_pos := position(' ' in p_given_name);
        
        IF v_space_pos > 0 THEN
            -- Case 1: Multiple words
            out_last_name := substring(p_given_name from 1 for v_space_pos - 1);
            out_first_name := substring(p_given_name from v_space_pos + 1);
        ELSE
            -- Case 3: Single word
            out_last_name := p_given_name;
            out_first_name := '';
        END IF;
    
    -- Case 2 & 4: Only p_surname is provided
    ELSIF (p_given_name IS NULL OR p_given_name = '') AND p_surname IS NOT NULL AND p_surname != '' THEN
        v_space_pos := position(' ' in p_surname);
        
        IF v_space_pos > 0 THEN
            -- Case 2: Multiple words
            out_last_name := substring(p_surname from 1 for v_space_pos - 1);
            out_first_name := substring(p_surname from v_space_pos + 1);
        ELSE
            -- Case 4: Single word
            out_last_name := p_surname;
            out_first_name := '';
        END IF;
    
    -- Both parameters are provided
    ELSE
        out_last_name := p_surname;
        out_first_name := p_given_name;
    END IF;

-- Convert to uppercase if required
    IF v_is_force_uppercase THEN
        out_last_name := upper(out_last_name);
        out_first_name := upper(out_first_name);
    END IF;

END;

sp_parse_guest_name

Parameters

Definition