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

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 !,"---------",!
}

}

Result:

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

Notes:

• 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 ;)

See Indexing of non-atomic attributes

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.