I have two strings as below:

_var_1 text := '815 PAADLEY ROAD PL';
_var_2 text := 'PAADLEY ROAD PL';
_var_3 text;

I want to merge these two strings into one string and to remove duplicates:

_var_3 := _var_1 || _var_2; 

As a result, the variable (_var_3) should contain only - 815 PAADLEY ROAD PL without dublicate. Can you advise or help recommend any PostgreSQL feature?

I read the documentation and could not find the necessary string function to solve this problem... I am trying to use regexp_split_to_table but nothing is working.

I tried to use this method, but it's not what I need and the words in the output are mixed up::

WITH ts AS (
    SELECT
        unnest(
            string_to_array('815 PAADLEY ROAD PL PAADLEY ROAD PL', ' ')
        ) f
)
SELECT
    f
FROM ts
GROUP BY f

-- f
-- 815
-- ROAD
-- PL
-- PAADLEY

CodePudding user response：

I assume you want to treat strings as word lists and then you have to concat them like they were a sets to be unioned, with retaining order. This is basically done by following SQL:

with splitted (val, input_number, word_number) as (
  select v, 1, i
  from unnest(regexp_split_to_array('815 PAADLEY 2 ROAD 3 PL',' ')) with ordinality as t(v,i)
  union
  select v, 2, i
  from unnest(regexp_split_to_array('PAADLEY ROAD 4 PL',' ')) with ordinality as t(v,i)
), numbered as (
  select val, input_number, word_number, row_number() over (partition by val order by input_number, word_number) as rn
  from splitted
)
select string_agg(val,' ' order by input_number, word_number)
from numbered
where rn = 1 

fiddle

However this is not kind of task to be solved in SQL in smart and elegant way. Moreover, it is not clear from your specification what to do with duplicate words or if you want to process multiple input pairs (both requirements would be possible, though SQL is probably not the right tool). At least please provide more sample inputs with expected outputs.