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



  • 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!



    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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2