CODEWITHSUNDEEP
sqlsql-serverperformancetrim
abbas
abbas

Reputation: 432

Sql Server - Alternative of doing a RTRIM/LTRIM in the where clause

I hava a column name which is a varchar

I want to filter all results where name is an empty string...

 select name 
 from tblNames
 where name <> ''

What I want to do is:

 select name 
 from tblNames
 where Ltrim(RTrim(name)) <> ''

I want to apply a trim on name in the where clause but I have read a few articles mentioning the performance issue of functions inside the where clause

I want a solution to this without hurting performance

Upvotes: 5

Views: 30021

Answers (3)

Imre
Imre

Reputation: 504

While

'abc' = 'abc '

(with spaces after the string on the right hand side of the equasion) is TRUE

'abc' = ' abc'

(with spaces before the string on the right hand side of the equasion) is FALSE.

Therefore what is automatically ignored is the trailing spaces only (works like RTRIM but not like LTRIM).

Upvotes: 3

johnnycrash
johnnycrash

Reputation: 5344

You could make a constraint that only trimmed data goes in the field.

You could make an index on LTRIM(RTRIM(name)). SQL Might be smart enough to use it.

You could make a calculated field that is LTRIM(RTRIM(name)), index that field, and then use that field in your query.

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115610

Standard behaviour in SQL-Server is that

'      ' = ''

is TRUE, because trailing spaces are ignored. From MSDN support:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. ...

So, your condition WHERE name <> '' should work fine, and not include any strings where there are only spaces.

Upvotes: 13

Related Questions