Geek asks and answers

Sql Server - an alternative to doing RTRIM / LTRIM in the where clause

I have a column name which is varchar

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

 select name 
 from tblNames
 where name <> ''

What I want to do:

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

I want to apply clipping by name in the where clause, but I read several articles that mention the performance issue of functions inside the where clause

I want a solution for this without sacrificing performance

+3
source share
3 answers

The standard behavior in SQL Server is that

'      ' = ''

is TRUEsince trailing spaces are ignored. From MSDN Support :

SQL Server ANSI/ISO SQL-92 ( 8.2, № 3) , . ANSI , , . WHERE HAVING Transact-SQL. , Transact-SQL 'abc' 'abc ' .

LIKE....

, WHERE name <> '' , .

+10

, .

LTRIM(RTRIM(name)). SQL , .

LTRIM(RTRIM(name)), , .

+4

While

'abc' = 'abc '

(with spaces after the line on the right side of the equality) TRUE

'abc' = ' abc'

(with spaces before the line on the right side of the equality) FALSE.

Therefore, what is automatically ignored is only trailing spaces (works like RTRIM, but doesn't like LTRIM).

0
source

More articles:


All Articles