trim() function in WHERE clause

Hello all.
I would like to know how you handle this scenario:
1. I have a table where one field is of the type str, and the users enter there data with a space sometimes without spaces, eg "0011" or "[space] 0011" or even "00 [space] 11". Such forms are correct and so it should remain in the database.
2. I have a variable that is always in the form "0011" (no space). This variable is a condition for the WHERE clause.

Question: how to construct select with TRIME in the table field.
(Write in pseudo code) SELECT * FROM my_table WHERE trime (mytable.myField) == "0011"

How to do this in X ++ [2012r3]?

  • It seems weird to me. If "0011" or "[space] 0011" represents the same thing, you clearly have rubbish data in database and you should solve this problem, not some other problem. And if they're completely different things, what's the point of the query.

    You can't use trim() in WHERE clause and even if you could, it would be bad for performance, because it couldn't use the index.

    There are ways how to achieve what you asked for, but it seems that you really have problem with data and not with code.