Q:

Pulling Variable Data in Database with IN/LIKE, using TRIM, or CONTAINS in MySQL

So the problem that I have run into may be a problem that has been asked before but I have some supplemental questions to add to it. I've seen other Stackoverflow questions in regards to the inability to use IN and LIKE in the Where clause. The only problem is that I am trying to steer away from using LIKE and OR, because in PHP I am putting data into an array and imploding the data with (',') so I can use IN, in some instances the data could be 100s of entrys and it just doesnt seem safe to use LIKE with the possibility of a variety of combinations. I've had someone at work mention that I could use TRIM, but I feel like that wont solve my issue. With that said, let me provide some insight.
I pull data from a directory where the username is [UserName], this username or in some cases, multiple user names gets stored into an array and a while loop runs to get Room Numbers based off the username. The problem is that this table (Which is Read-Only, and apparently no one knows who has access to make changes) that contains room numbers has the username as [  UserName]. Because of it, I will never get that data because [UserName] and [  UserName] are obviously not the same.
I need to find a way to write a more solid query that basically looks at each username like this: %UserName%
Or just pretty much negates the white spaces in the database while using a WHERE/IN clause (or something similar).

A:

I fail to see how using TRIM will not work...
Option 1:
SELECT u.*, TRIM(u.username) as username_canonical
FROM user u
WHERE TRIM(u.username) = ?

Option 2:
SELECT u.*, TRIM(u.username) as username_canonical
FROM user u
HAVING username_canonical = ?

With either of those you could still use an IN clause in addition to you username criteria.

