How do you find a field that only has a different spell?

[Raziyah00]

Good afternoon!

In the BDE PostgreSQL, the tables contain with the names of the organizations. We need to find the same without a word order. The divider is one or more gaps.

Examples: 'ECOOFLON FOR CNPC' = 'ECOFLON NPC' 'GARANT NGO zaO' = 'GARANT ON NGOs'

Can you tell me how to do this better?

More grateful!

[courtlanda]

1. Turn the lines into the masses using the function. regexp_split_to_array
2. We use "contain" operators.@>and "contain"<@() To determine the complete congruence of the masses (excluding the sequence of elements). If A contains B and A is contained in B, the masses are completely identical.

The request for a stamp of such records from the wtest table is:

with Q as (
select a.*,regexp_split_to_array(txt,' +') arr
  from wtest a
)
select * from Q a, Q b
 where a.arr <@ b.arr and a.arr @> b.arr and a.id!=b.id

http://sqlfiddle.com/#!15/09f04/2

Option 2. We'll also transform it into words, sort it and get it back in line. The scattered rows can be compared or, for example, dispersed with a sample so that such rows may be near:

select a.*,
       array_to_string(array(select unnest(regexp_split_to_array(txt,' +')) AS x order by x),' ') srt
  from wtest a
 order by srt