How does SQL server treat text comparisons with LEADING spaces

0
votes

Much is made (and easily able to be found on the internet) about how you do not need to use where rtrim(columnname) = 'value' in sql server, because it automatically considers a value with or without trailing spaces to be the same.

However I've had a hard time finding info about LEADING spaces. What if (for whatever reason) our data warehouse has leading spaces on certain varchar / char type of fields and we need to have where clauses - do we still need where ltrim() ? I'm trying to avoid this big performance hit by researching out other options. Thank You

1
sql-serversql-functiontext-comparison
trailing spaces are ignored in comparison but leading spaces are not. This is simple to test. select 1 where 'this' = ' this' – Sean Lange
Yes, I did perform that quick and easy test, but I was dubious about that test covering all text-like datatypes, in their relative multitude. So if there is the possibility of leading spaces, I just have to perform the ltrim, basically - no way out of it? – Isaac
If you want 'test' to = ' test' you will have to either do an ltrim on the right side or add a space to the left side. Leading spaces are not ignored. – Sean Lange

1 Answers

0
votes

Leading spaces are never ignored in comparisons of any text based data type. If you are comparing the equality of text columns, the best option is to validate your values on data entry to make sure that text with unwanted spaces in front is not allowed. For example if your database is expecting a user to type something from a list of possible values that your database application is expecting, do not allow your user interfaces to let users enter the text free-form, force them to enter one of the explicit valid values. If you need the user to be able to enter free-form text but never want leading spaces, then strip them on the insert. Normalizing your database should prevent a lot of these types of issues.