Make a slug in PostgreSQL translating diacritics

20 December 2010

I needed to make slugs in Postgresql from names which included diacritics. Though not complete, here’s a reasonable stab at it:

select regexp_replace(translate(replace(lower(your_field_name_here), ' ', '-'), 'áàâãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ','aaaaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'), '[^\w -]', '', 'g') as slug

The translate is not quite right because it translates some uppercase to lowercase, but it’s all lowercase for the slug, so it doesn’t matter here.

[Update] Added ‘g’ flag to regexp_replace to replace all occurrences, not just first.

Filed under: Postgresql — Scott @ 9:09 pm

1 Comment »

  1. Very great function.
    I used it for creating a postgresql function getslug :

    CREATE OR REPLACE FUNCTION getslug(texte varchar) RETURNS VARCHAR AS
$$
DECLARE
    result varchar;
BEGIN
    result := replace(texte , 'æ', 'ae');
    result := replace(result , 'œ', 'oe');
    result := replace(result , '€', 'euros');
    result := replace(result , '$', 'dollars');
    result := replace(result , '£', 'pound');
    result := replace(result , '¥', 'yen');
    result := regexp_replace(translate(replace(lower(result), ' ', '-'), 
        'áàâãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮçÇÿ&,.ñÑ',
        'aaaaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuuccy_--nn'), E'[^\\w -]', '', 'g');
    RETURN result;
END;
$$
LANGUAGE PLPGSQL;

    Comment by Mehira — 31 January 2011 @ 10:11 am

RSS feed for comments on this post.

Leave a comment