REGEXP_LIKE in Postgresql

Question

I have a table to store substitutions which is including two fields, the first one is to store the word, the second is to store the substitutions. I know the creation of the table is not a suitable approach, but it is already in place and used by other systems.

The table look like the following:

WORD        SUBS_LIST
------------------------------------
MOUNTAIN    MOUNTAIN, MOUNT, MT, MTN
VIEW        VIEW, VU
FIFTH       V, 5TH
YOU         EWE, U , YEW
ROW         ROW , ROE
ONE         UN , ONE

Then, when a name comes in it is substitute according to the table. I was able to the previous on Oracle using regexp_like. However, I would like to apply the same in Postgresql. I have attempt using ~ to replace regexp_like and regexp_matches without success.

Please find here the DB<>Fiddle which I have tried so far.

Thank you for your help :)

It would have been better to include your attempts from the fiddle into your question to make it clearer what you are trying to achieve. — , Nov 28 '19 at 07:27

score 3 · Accepted Answer · 2019-11-28T07:26:49.417

You don't need regexp for that. If I understood you correctly you want to input a word, search the elements in sub_list and return the word column for that. This is best done by converting the (ugly) comma separated list to an array, then use the ANY operator:

select word
from the_table
where 'mount' = any(string_to_array(subs_list, ','));

The above would deal properly with the whitespace you have around the , - not sure if that is a result of your formatting or if you really store the list that way. If you do need to deal with whitespaces, you can use the following:

select word
from the_table
where exists (select *
              from unnest(string_to_array(subs_list, ',')) as x(subs)  
              where trim(x.subs) = 'mount');

If your input is a list of words, you can use regexp_split_to_table() to turn the input words into rows and join to the substitutions.

SELECT w.input, coalesce(x.word, w.input) as word
FROM regexp_split_to_table('MOUNT VU FOOD CAFE', '\s') as w(input) 
  LEFT JOIN (
    select s.word, trim(s1.token) as token
    from subs s
      cross join unnest(string_to_array(s.subs_list, ',')) s1(token)
  ) as x on lower(trim(w.input)) = lower(x.token)
;

Online example: https://rextester.com/DZBF77100

REGEXP_LIKE in Postgresql

1 Answers1