CODEWITHSUNDEEP
sqlsql-servert-sqlquery-optimization
Chad Baldwin
Chad Baldwin

Reputation: 2602

LTRIM / RTRIM - But only where First/Last character is a space?

Not sure where else I can ask this...but I'm going through some code in our system and came across this in one of our data cleansing procs...

UPDATE #X SET
     Email = CASE
                 WHEN LEFT(Email, 1) = ' ' OR RIGHT(Email, 1) = ' '
                 THEN LTRIM(RTRIM(Email))
                 ELSE Email
             END

This seems pointless to me, and not sure why it wasn't just written as:

UPDATE #X SET Email = LTRIM(RTRIM(Email))

Is there some benefit I'm not aware of or possibly some data issue, like an implicit conversion error or something that it's avoiding? There is a lot of unnecessary code like this that I would like to start cleaning out.

There is no apparent time savings with it. I ran Statistics IO and Time as well as the query plan and it's all identical.

Upvotes: 2

Views: 667

Answers (2)

Roger Wolf
Roger Wolf

Reputation: 7712

It might be possible that this code has been written for a version of SQL Server prior to 2005. In it, Microsoft has added an optimisation to update that actually skips modifications if original value for a column is identical to the new one (i.e. no actual changes made). In 2000 and previous versions, all affected rows were treated as changed and consequently put into the transaction log, thus bloating it up.

However, as Gordon has noted it, even in this case the original author got it wrong. The condition should have been put into WHERE, because case doesn't filter any rows. So yes, you are right, this can and should be cleaned up.

P.S. And if you are already on 2017 version, there is finally TRIM() function available, which is actually much more than just an ltrim(rtrim()) equivalent.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

It is pointless, but for a different reason. It should probably be written as:

UPDATE #X
    SET Email = LTRIM(RTRIM(Email))
    WHERE Email LIKE ' %' OR EMAIL LIKE '% ';

There is no reason to attempt the update, if the value is not going to change.

Notes:

  • SET should really go on the first line where values are being set. Okay, this is an aesthetic opinion.
  • I think LIKE is clearer in looking for the spaces. Plus it is more powerful, in the sense that it can look for groups of characters. And, it can be sargable, allowing the use of an index (although not in this case).

Upvotes: 4

Related Questions