singularize sql function

singularize.sql

-- https://github.com/markmcspadden/rails/blob/master/activesupport/lib/active_support/inflections.rb
CREATE TEMP FUNCTION singularize(input_string STRING)
RETURNS STRING
AS (
  CASE
    WHEN REGEXP_CONTAINS(input_string, '(database)s$') THEN REGEXP_REPLACE(input_string, '(database)s$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(quiz)zes$') THEN REGEXP_REPLACE(input_string, '(quiz)zes$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(matr)ices$') THEN REGEXP_REPLACE(input_string, '(matr)ices$', '\\1ix')
    WHEN REGEXP_CONTAINS(input_string, '(vert|ind)ices$') THEN REGEXP_REPLACE(input_string, '(vert|ind)ices$', '\\1ex')
    WHEN REGEXP_CONTAINS(input_string, '^(ox)en$') THEN REGEXP_REPLACE(input_string, '^(ox)en$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(alias|status)es$') THEN REGEXP_REPLACE(input_string, '(alias|status)es$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(octop|vir)i$') THEN REGEXP_REPLACE(input_string, '(octop|vir)i$', '\\1us')
    WHEN REGEXP_CONTAINS(input_string, '(cris|ax|test)es$') THEN REGEXP_REPLACE(input_string, '(cris|ax|test)es$', '\\1is')
    WHEN REGEXP_CONTAINS(input_string, '(shoe)s$') THEN REGEXP_REPLACE(input_string, '(shoe)s$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(o)es$') THEN REGEXP_REPLACE(input_string, '(o)es$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(bus)es$') THEN REGEXP_REPLACE(input_string, '(bus)es$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(m|l)ice$') THEN REGEXP_REPLACE(input_string, '(m|l)ice$', '\\1ouse')
    WHEN REGEXP_CONTAINS(input_string, '(x|ch|ss|sh)es$') THEN REGEXP_REPLACE(input_string, '(x|ch|ss|sh)es$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(m)ovies$') THEN REGEXP_REPLACE(input_string, '(m)ovies$', '\\1ovie')
    WHEN REGEXP_CONTAINS(input_string, '(s)eries$') THEN REGEXP_REPLACE(input_string, '(s)eries$', '\\1eries')
    WHEN REGEXP_CONTAINS(input_string, '([^aeiouy]|qu)ies$') THEN REGEXP_REPLACE(input_string, '([^aeiouy]|qu)ies$', '\\1y')
    WHEN REGEXP_CONTAINS(input_string, '([lr])ves$') THEN REGEXP_REPLACE(input_string, '([lr])ves$', '\\1f')
    WHEN REGEXP_CONTAINS(input_string, '(tive)s$') THEN REGEXP_REPLACE(input_string, '(tive)s$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(hive)s$') THEN REGEXP_REPLACE(input_string, '(hive)s$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '([^f])ves$') THEN REGEXP_REPLACE(input_string, '([^f])ves$', '\\1fe')
    WHEN REGEXP_CONTAINS(input_string, '(^analy)ses$') THEN REGEXP_REPLACE(input_string, '(^analy)ses$', '\\1sis')
    WHEN REGEXP_CONTAINS(input_string, '((a)naly|(b)a|(d)iagno|(p)arenthe|(p)rogno|(s)ynop|(t)he)ses$') THEN REGEXP_REPLACE(input_string, '((a)naly|(b)a|(d)iagno|(p)arenthe|(p)rogno|(s)ynop|(t)he)ses$', '\\1\\2sis')
    WHEN REGEXP_CONTAINS(input_string, '([ti])a$') THEN REGEXP_REPLACE(input_string, '([ti])a$', '\\1um')
    WHEN REGEXP_CONTAINS(input_string, '(n)ews$') THEN REGEXP_REPLACE(input_string, '(n)ews$', '\\1ews')
    WHEN REGEXP_CONTAINS(input_string, 's$') THEN REGEXP_REPLACE(input_string, 's$', '')
    ELSE input_string
  END
);

select word, singularize(word) as singular_word from unnest(['histories', 'statuses', 'experiences', 'users', 'companies', 'goods', 'posts', 'words', 'fish']) as word;