Address/Prevent DB Strings With Leading, Trailing, or Multiple Consecutive Spaces

[aaronskiba]

Please complete the following fields as applicable:

What version of the DMPRoadmap code are you running? (e.g. v2.2.0)

• v4.1.1

Description

• Throughout the db, there are many columns within many tables where strings start or end with a space, or have multiple consecutive spaces. To provide some examples, the following queries were performed on a May 2024 db dump from DMP Assistant's production environment:

SELECT COUNT(*)
FROM users
WHERE firstname LIKE ' %'
OR firstname LIKE '% '
OR surname LIKE ' %'
OR surname LIKE '% ';
-------------------------
count: 336

SELECT COUNT(*)
FROM orgs
WHERE name LIKE ' %'
OR name LIKE '% '
OR name LIKE '%  %'; -- more than one space
-------------------------
count: 12

SELECT COUNT(*)
FROM plans
WHERE title LIKE ' %'
OR title LIKE '% '
OR title LIKE '%  %'; -- more than one space 
-------------------------
count: 481

These extra spaces can lead to unwanted outcomes when executing search. The following is one example:

• In the users table, there is an entry where firstname = "Susan " and surname = "Brown"

• Searching "Susan Brown" (one space) returns no results:

• The query only works when "Susan Brown" (two spaces) is searched: