Skip to content

Instantly share code, notes, and snippets.

@lukeramsden
Created June 24, 2020 08:01
Show Gist options
  • Save lukeramsden/de956a2bf2c9c8bb9d091e6ffeb38dd0 to your computer and use it in GitHub Desktop.
Save lukeramsden/de956a2bf2c9c8bb9d091e6ffeb38dd0 to your computer and use it in GitHub Desktop.
PL/pgSQL implementation of a LexoRank-style lexicographical rank generator function
-- https://stackoverflow.com/questions/38923376/return-a-new-string-that-sorts-between-two-given-strings
create or replace function app_public.mid_string(prev text, next text) returns text as $$
declare
v_p int;
v_n int;
v_pos int := 0;
v_str text;
begin
LOOP -- find leftmost non-matching character
v_p := CASE WHEN v_pos < char_length(prev) THEN ascii(substring(prev from v_pos + 1)) ELSE 96 END;
v_n := CASE WHEN v_pos < char_length(next) THEN ascii(substring(next from v_pos + 1)) ELSE 123 END;
v_pos := v_pos + 1;
EXIT WHEN NOT (v_p = v_n);
END LOOP;
v_str := left(prev, v_pos-1); -- copy identical part of string
IF v_p = 96 THEN -- prev string equals beginning of next
WHILE v_n = 97 LOOP -- next character is 'a'
-- get char from next
v_n = CASE WHEN v_pos < char_length(next) THEN ascii(substring(next from v_pos + 1)) ELSE 123 END;
v_str := v_str || 'a'; -- insert an 'a' to match the 'a'
v_pos := v_pos + 1;
END LOOP;
IF v_n = 98 THEN -- next character is 'b'
v_str := v_str || 'a'; -- insert an 'a' to match the 'b'
v_n := 123; -- set to end of alphabet
END IF;
ELSIF (v_p + 1) = v_n THEN -- found consecutive characters
v_str := v_str || chr(v_p); -- insert character from prev
v_n = 123; -- set to end of alphabet
v_p := CASE WHEN v_pos < char_length(prev) THEN ascii(substring(prev from v_pos + 1)) ELSE 96 END;
WHILE v_p = 122 LOOP
v_pos := v_pos + 1;
v_str := v_str || 'z'; -- insert 'z' to match 'z'
v_p := CASE WHEN v_pos < char_length(prev) THEN ascii(substring(prev from v_pos + 1)) ELSE 96 END;
END LOOP;
END IF;
return v_str || chr(ceil((v_p + v_n) / 2.0)::int);
end;
$$ language plpgsql strict immutable;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment