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.

  • In reply to Martin Dráb:

    Martin, thank you for answering. Do not bother with the quality of data in the database (no such question).
    To have a didactic value we imagine that I pick up the bank statement where the account number in file is saved without spaces. By contrast, in the BankAccountTable table, I have account with spaces.
    In such a situation I would like to perform a trim function in the IBAN field.
    Kindly request to stick to the thread.

  • In reply to Artur:

    You're making a mistake by keeping your database in inconsistent state. You're trying to implement a poorly performing workaround instead of fixing the actual problem.
    I already answered your question - using trim() in WHERE isn't possible. You could technically create a computed column returning the data in the form in which they should be stored in database, but fixing the data in the first place makes much a better sense.

    Otherwise have with your inconsistent data. :-)
  • In reply to Martin Dráb:

    thx anyway.