Question 89227 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    This iterates over each character position of the strings by generating a series of the length, so both strings must be the same length. If any character in a position is o then o else a.

    SELECT 
    STRING_AGG(
        CASE 
            WHEN POSITION('o' IN string1) = position OR POSITION('o' IN string2) = position THEN 'o'
            WHEN SUBSTRING(string1 FROM position FOR 1) = 'a' AND SUBSTRING(string2 FROM position FOR 1) = 'a' THEN 'a'
            ELSE 'o'
        END,
        ''
    ) AS result
FROM 
    (SELECT 'oaaoaa' AS string1, 'aoaoao' AS string2, generate_series(1, LENGTH('oaaoaa')) AS position) t
    result
    ooaoao

    fiddle

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search