SQLで共通の開始文字列を抽出する

やりたいこと

このようなレコードから「東京都港区芝公園４丁目」を抽出したい。

• 東京都港区芝公園４丁目２−８ 東京タワー
• 東京都港区芝公園４丁目７−３５ 増上寺

なかなか標準で用意されているものだけでは解決できなくて、結果、集約関数を実装して解決した。

$ psql --version
psql (PostgreSQL) 14.13 (Homebrew)

create or replace function intersect_prefix(state text, next_value text)
returns text language plpgsql as $$
declare
  subject text;
begin
  if state is null then
    return next_value;
  else
    subject := next_value;
    loop
  	  if length(subject) is null then
        return null;
  	  end if;

  	  if starts_with(state, subject) then
        return subject;
      end if;

      subject := substring(subject, 1, length(subject) - 1);
    end loop;
  end if;
end;
$$;

create or replace aggregate intersect_prefix(text)
( sfunc = intersect_prefix,
  stype = text);

検証

SELECT
  intersect_prefix(column1)
FROM (
  VALUES ('東京都港区芝公園４丁目２−８ 東京タワー'), 
         ('東京都港区芝公園４丁目７−３５ 増上寺'));

-- 結果
'東京都港区芝公園４丁目'