How to find all records where a textfield contains "04/06/2020" twice? [duplicate]

Question

this is a bit of an odd one.

Say I need to find all Accounts where the textarea field "Notes__c" contains the text 04/06/2020 twice, how would I go about that? Ideally using Apex or Reports.

Basically the Notes field looks like this:

"04/06/2020 RT - rob did something

04/06/2020 BD - bobby did this

03/06/2020 JD - john did that"

I need to find all records where the Notes contain "04/06/2020" twice, like in the example above. Could anyone help me with this?

Answer 1

As Phil W mentioned, you cannot filter long text fields in SOQL. You can vote for the idea to allow this.

You didn't really specify the context for this. If it's a trigger context, then you already have the records. I'd be worried about your query size if you have millions of accounts and no real way to narrow down which ones you want to check the Notes of in a SOQL query. If you have some other fields you can utilize and are sure you could narrow it down then it should work fine.

Once you have a list of accounts, it's easier. You can check how many matches you find to your substring in a given long text area field with countMatches() as below:

Specific to your scenario, a simple for loop like this would work:

for(Account acc : accountList){
    if(acc.Notes__c.countMatches('04/06/2020') == 2){
        //do whatever you need to do
    }
}

I'm not sure how you're building what date to find, but you'd want to make it dynamic (based on today, etc) or a label that you can change when you want to run and check. As mentioned above, depending on the context (trigger execution, scheduled job), be aware of any SOQL limits with the returned rows.

Answer 2

Taking Kris's answer a little further, if the context is outside the Account trigger, in order to get a set of candidate Accounts that may be what you are looking for - i.e. ones that include the search term at least once, somewhere (may not be in your long text field, but it may be) - tha you can then filter down, try something like:

String searchTerm = '04/06/2020';
List<List<SObject>> results = [FIND :searchTerm IN ALL FIELDS RETURNING Account (Id, Notes__c)];
List<Account> accounts = new List<Account>();

if (!results.isEmpty()) {
    for (Account account : (List<Account>) results[0]) {
        if (account.Notes__c != null && account.Notes__c.countMatches(searchTerm) == 2) {
            accounts.add(account);
        }
    }
}

The SOSL will match any account that has your search term in any field, including long text area fieds, and thus your Note__c field. The for loop then filters out false positive matches, leaving the accounts list containing just those that have the search term exactly twice in the Notes__c field.

If your search terms are likely to occur frequently in other fields, try to add extra filtering by including a WHERE clause in the SOSL against one or more additional fields that helps select instances you want to search in (just not using an unfilterable field like Notes__c itself).

Answer 3

You can try something like below, it should work. I have assumed date is in the format DD/MM/YYYY and the string always starts with a date. You can manipulate below code in the multiple ways.

string str = '04/06/2020 RT - rob did something 04/06/2020 BD - bobby did this 03/06/2020 JD - john did that'; integer k =0,m=10; String s1; set<string> dateset = new set<string> (); while( k != -1){ s1 = str.substring(k,m); if(dateset.contains(s1)){ system.debug('duplicate date'); } else{ dateset.add(s1); } k = str.indexof('0',m+1); m = k+10; }