Postgres - sort and compare strings

Question

I have the below table:

create table test(
  A1 varchar,
  A2 varchar,
  A3 varchar,
  B1 varchar,
  B2 varchar,
  B3 varchar
);
  
insert into test values('How', 'Are', 'You', 'You', 'How', 'Are');
insert into test values('How', null , 'You', 'You', 'How', null);

I want to concatenate all A strings and all B strings, sort them and compare the 2. This gives me the concatenation:

select (coalesce(A1, '') || ' ' || coalesce(A2,'') || ' ' || coalesce(A3, '')) as A,
(coalesce(B1, '') || ' ' || coalesce(B2,'') || ' ' || coalesce(B3, '')) as B
from test;

This is the output:

A           B
How Are You You How Are
How You     You How

As seen, both of these strings are same if we sort them and compare. How can I do this in Postgres?

Here is the fiddle to try:

http://sqlfiddle.com/#!15/b78281/6

Zakaria · Accepted Answer · 2022-02-14 10:33:09Z

1

Would've been easier if you had an id column, but row_number() can be used. You can then union all all the A* columns into one A column, same with B*. Then you can concatenate using string_agg where you can choose how the words are to be sorted:

with u as
(select *, row_number() over(order by A1, A2, A3, B1, B2, B3) as rn
from test),
v as
(select A1 AS A, B1 as B, rn from u
union all select A2 AS A, B2 as B, rn from u
union all select A3 AS A, B3 as B, rn from u)
select string_agg(A, ' ' order by A) as A, string_agg(B, ' ' order by B) as B
from v
group by rn

Fiddle

answered Feb 14, 2022 at 10:33

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - sort and compare strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related