How merge two the same strings of lenght with delta in postgresql?

Question

For example, I have the strings oaaoaa and aoaoao. I need the result: ooaoao. That is, it is necessary that the lines merge into one. At the same time, the common part of the lines was preserved, and the mismatch was replaced by the symbol

How can this be done in Postgresql?

I try make this:

select 'text' | 'text'

but but of course got an operator error.

Answer 1

You chose the wrong data type. You should have taken varbit rather than text or varchar, then the | would have done just what you want. You can still achieve your goal by translating the characters to 0 and 1 and casting the data to `varbit':

SELECT translate(
          CAST (
             CAST (translate('oaaoaa', 'ao', '01') AS varbit) |
             CAST (translate('aoaoao', 'ao', '01') AS varbit)
             AS text
          ),
          '01', 'ao'
       );

 translate 
═══════════
 ooaoao
(1 row)