Extension for String Translation

PostgreSQL provides a built-in function for character wise string replacement:
select translate('abcdefghijkl', 'ace', '123');
translate
--------------
1b2d3fghijkl

pgstrtranslate extends it with multi-character replacement. It takes 4 arguments and returning a text.
CREATE OR REPLACE FUNCTION public.pgstrtranslate(
    fullsearch boolean,
    t text,
    search text[],
    replacement text[])
  RETURNS text AS
'$libdir/pgstrtranslate', 'pgstrtranslate'
    LANGUAGE c IMMUTABLE STRICT;

How it works

Non-fullsearch replacement:

select pgstrtranslate(false, --non-fullsearch
    'abcdefghijkl', --original string
    array['ab', 'efg', '2cd']::text[], --array of searchs
    array['012', '3', '78']::text[]); --array of replacement
translate
--------------
012cd3hijkl

abcdefghijkl’ -> ‘012cd3hijkl’
Note that ‘2cd’ does not match original string.

Fullsearch replacement:

select pgstrtranslate(true, --fullsearch
    'abcdefghijkl', --original string
    array['ab', 'efg', '2cd']::text[], --array of searchs
    array['012', '3', '78']::text[]); --array of replacement
translate
--------------
01783hijkl

Replace ‘ab’ with ‘012’: ‘abcdefghijkl’ -> ‘012cdefghijkl’
Replace ‘efg’ with ‘3’: ‘012cdefghijkl’ -> ‘012cd3hijkl’
Replace ‘2cd’ with ’78’: ’012cd3hijkl’ -> ’01783hijkl’

How to install

  1. Clone or download source code from https://github.com/AbdulYadi/pgstrtranslate.git. Extract it.
  2. If necessary, modify PG_CONFIG path according to your specific PostgreSQL installation location.
  3. Build as usual:
  4. $ make
    $ make install

  5. On successful compilation, install this extension in PostgreSQL environment:
  6. $ create extension pgstrtranslate;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: