Select if string contains substring only once

Question

I'm trying to make a report that pulls data between two dates and where a string occurs only once in the description column which has a text datatype

I've tried using a solution from the "postgresql-select-if-string-contains" stackoverflow question but it didn't account for the amount of how many times the string occurs in the description block.

SELECT o.ownername, to_char(a.auditdate, 'MM/DD/YYYY') as dateday, 
o.additionalflags, 
o.emailaddress, o.id, a.description, a.username, 
CASE WHEN a.Action = 0 THEN 'ADD'
WHEN a.Action = 1 THEN 'EDIT'
WHEN a.Action = 2 THEN 'DELETE'
WHEN a.Action = 3 THEN 'MOVE'
WHEN a.Action = 4 THEN 'LOGIN'
WHEN a.Action = 5 THEN 'LOGOUT'
END AS Action
FROM owner o
INNER JOIN audittrail a ON o.id = a.linkid
/*
WHERE array_length(regexp_matches(description, '1\-Gets Email'), 1) = 1
*/
AND a.auditdate >= '$ASK DATE Enter the from date$' 
AND a.auditdate <= '$ASK DATE Enter the to date$'
ORDER BY o.ownername

The column I'm trying to sift through has data that looks like this:

(ID 2) >>> LASTCHANGEDDATE: 2011-11-11 11:11:11.653868 ==> 2018-12-23 14:24:28.694724, ADDITIONALFLAGS: *1-Atest|1-Gets Email|1-Gets Snail Mail|Adopter-|donor|driver|fosterer|homechecked|homechecker|member|V-Foster Home: short-term/emergency|* ==> **1-Gets Email|1-Gets Snail Mail|Adopter-NCGSPR Dog|donor|driver|fosterer|homechecked|homechecker|member|V-Foster Home: short-term/|volunteer|,**

The column pretty much is a description log of what has changed in the database. The italicized words are what ADDITIONALFLAGS are the past and the bold words are the new and current ADDITIONALFLAGS after the change. I'm looking for description rows where '1-Gets Email' once because I only care about ones that have changed and not ones that had '1-Gets Email' before the dates I queried for.

Answer 1

Somewhat hacky, but should work: Check if the length of the description minus the length the text you search for is equal to the lenght of the description where the text you search for is replaced with the empty string.

...
WHERE length(description) - length('1-Gets Email') = length(replace(description, '1-Gets Email', ''))
...

Or a more generic approach: Check the number of matches regexp_matches() found, in this case 1.

...
WHERE (SELECT count(*)
              FROM regexp_matches(description, '1\-Gets Email', 'g')) = 1
...

Answer 2

split_part() can be used for a simple and fast solution:

...
WHERE  description ~ '1-Gets Email'
AND    split_part(description, '1-Gets Email', 3) = '';

This treats the search pattern as delimiter. If there is no second delimiter, there is not third part - empty in this case.
If the search pattern could appear at the start or end of the string we could get false positives, but that corner case can be ruled out in your case since values are enclosed in custom delimiters.

If the table is big you might add a trigram index to support the first WHERE clause (description ~ 'pattern') and make it much faster. Like:

CREATE INDEX tbl_description_gin_trgm_idx ON tbl USING gin (description gin_trgm_ops);

See:

• PostgreSQL LIKE query performance variations