How to find duplicates for a large text field in Caché Objects?

For best performance, you need index on this property. And request something like this.

SELECT Home_City,list(ID) ids 
FROM sample.person
GROUP BY Home_City
HAVING count(*)>1

And result will be something like this

You can try to use %SIMILARITY function available in Cache SQL.

having this done ~ 9 yrs back I can't resist to share my old solution (at that time for a UNIQUE on 2000  char)

- split Text into 4 sections in calculated properties e.g.
tx1 = $e(Text,1,500)
tx2 = $e(Text,501,1000) 
tx3 = $e(Text,1001,1500)
tx4 = $e(Text,1501,2000)

- index them
- then you can build an SQL  statement to have all 4 pieces identic.
you end up with a cascade of embedded SELECTS

It's not to fast but very precise.
 

Class Test.Duplicates Extends %Persistent
{

Index iText On (Text(KEYS), Text(ELEMENTS)) [ Type = bitmap ];

Property Text As %String(MAXLEN = 2000);

ClassMethod TextBuildValueArray(
  v,
  ByRef arr) As %Status
{
  #define sizeOfChunk 400

  i v="" {
    s arr("null")=v
  }else{
    s hasDupl=$$$YES
    f i=1:1:$l(v)\$$$sizeOfChunk+($l(v)#$$$sizeOfChunk>0) {
      s arr(i)=$e(v,1,$$$sizeOfChunk),$e(v,1,$$$sizeOfChunk)=""
      s:hasDupl&&'$d(^Test.DuplicatesI("iText"," "_i,##class(%Collation).SqlUpper(arr(i)))) hasDupl=$$$NO
    }
    s:hasDupl arr("hasDupl")=""
  }
  q $$$OK
}

ClassMethod Test()
{
  ;d ##class(Test.Duplicates).Test()

  d ..%KillExtent()
  
  s text=##class(%PopulateUtils).StringMin(2000,2000)
  &sql(insert into Test.Duplicates(Text)
  select 'ab'
  union all select 'abc' union all select 'abc'
  union all select 'abe' union all select 'abe' union all select 'abe'
  union all select null  union all select null
  union all select :text union all select :text)
  
  d $SYSTEM.SQL.PurgeForTable($classname()),
    $system.SQL.TuneTable($classname(),$$$YES),
    $system.OBJ.Compile($classname(),"cu-d")
    
  s sql(1)="select %ID,$extract(Text,1,10) Text10 from Test.Duplicates",
    sql(2)="select * from Test.Duplicates where FOR SOME %ELEMENT(Text) (%KEY='null')",
    sql(3)="select %ID,$extract(Text,1,10) HasDupl_Text10_All from Test.Duplicates where FOR SOME %ELEMENT(Text) (%KEY='hasdupl')",
    sql(4)="select distinct $extract(%exact(Text),1,10) HasDupl_Text10 from Test.Duplicates where FOR SOME %ELEMENT(Text) (%KEY='hasdupl')"
  
  f i=1:1:$o(sql(""),-1) w !,sql(i),!! d ##class(%SQL.Statement).%ExecDirect(,sql(i)).%Display() w !,"---------",!
}

}

USER>d ##class(Test.Duplicates).Test()
<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">%ID</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#808000">$extract</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">Text</FONT><FONT COLOR="#000000">,1,10) </FONT><FONT COLOR="#008000">Text10 </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">Test</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Duplicates</FONT>
ID      Text10
1       ab
2       abc
3       abc
4       abe
5       abe
6       abe
7
8
9       QfgrABXjbT
10      QfgrABXjbT
10 Rows(s) Affected
<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">* from </FONT><FONT COLOR="#008000">Test</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Duplicates </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#000000">FOR SOME %ELEMENT(</FONT><FONT COLOR="#008000">Text</FONT><FONT COLOR="#000000">) (</FONT><FONT COLOR="#008000">%KEY</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'null'</FONT><FONT COLOR="#000000">)</FONT>
ID      Text
7
8
2 Rows(s) Affected
<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">%ID</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#808000">$extract</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">Text</FONT><FONT COLOR="#000000">,1,10) </FONT><FONT COLOR="#008000">HasDupl_Text10_All </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">Test</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Duplicates </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#000000">FOR SOME %ELEMENT(</FONT><FONT COLOR="#008000">Text</FONT><FONT COLOR="#000000">) (</FONT><FONT COLOR="#008000">%KEY</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'hasdupl'</FONT><FONT COLOR="#000000">)</FONT>
ID      HasDupl_Text10_All
3       abc
5       abe
6       abe
10      QfgrABXjbT
4 Rows(s) Affected
<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">distinct </FONT><FONT COLOR="#808000">$extract</FONT><FONT COLOR="#000000">(%exact(</FONT><FONT COLOR="#008000">Text</FONT><FONT COLOR="#000000">),1,10) </FONT><FONT COLOR="#008000">HasDupl_Text10 </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">Test</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Duplicates </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#000000">FOR SOME %ELEMENT(</FONT><FONT COLOR="#008000">Text</FONT><FONT COLOR="#000000">) (</FONT><FONT COLOR="#008000">%KEY</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'hasdupl'</FONT><FONT COLOR="#000000">)</FONT>
HasDupl_Text10
abc
abe
QfgrABXjbT
3 Rows(s) Affected
---------

• not used hashing, so there's no risk of collisions
• support for strings of unlimited length up to 3641144 ($zu(96,39))
• customizable chunk size
• minimum code
• should work for older versions of Caché
• high performance
• possible else more optimize code ;)

I would probably go for some extra hashing safety and add a CRC or something similar to the hash. There may be more subtle hashing methods or combinations thereof that will make duplicates "almost impossible" ;)

I don't think that checking for duplicates can be solved other than have a reliable index per hash.

Note you can have more subtle hashing (hash sub-pieces, etc.) but you cannot have a reliable hasDuplicate property at runtime that goes beyond the current snapshot of data during INSERT.  IOW, it does not mean much in a system with lots of concurrent access.

In the end if will be 
 

SELECT COUNT(*) AS CountDuplicates
  FROM Test.Duplicates
 GROUP BY myHash
  HAVING COUNT(*) > 1

If you want to avoid duplicates you can make the index unique.