ltrim/rtrim syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ltrim/rtrim syntax

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2010-10-15 : 12:36:09

How do you remove leading and trailing blanks from a string?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-15 : 12:40:11

Use ltrim and rtrim.

CODO ERGO SUM

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2010-10-15 : 12:42:12

How do you use them together? I can use them one at a time.....

quote:
Originally posted by Michael Valentine Jones

Use ltrim and rtrim.

CODO ERGO SUM

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-10-15 : 15:12:07

ltrim(rtrim(YourValue))

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-15 : 15:23:17

ahhhh...the oppie said s/he wants to remove them from a column..

UPDATE table SET col = RTRIM(LTRIM(col))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Kristen
Test

22859 Posts

Posted - 2010-10-16 : 02:53:46

I would add to Brett's suggestion:

UPDATE table SET col = RTRIM(LTRIM(col))
WHERE col <> RTRIM(LTRIM(col))

to cut down the number of updates / amount of logging, particularly on a large table

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-16 : 16:34:38

good point...would be faster if they checked the length? gonna be a scan in any case

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-10-18 : 12:49:17

quote:
Originally posted by X002548

ahhhh...the oppie said s/he wants to remove them from a column..

S/he did?
All I saw was 'How do you remove leading and trailing blanks from a string?"

Nevertheless, in the spirit of the thread, I will improve on Kristen's improvement of Brett's Improvement of my implementation of Michael's solution:


Use [DeadHorse]
GO
UPDATE table SET col = RTRIM(LTRIM(col))
WHERE col <> RTRIM(LTRIM(col))

We would not want this run on just any database the OP happens to have set as their default, would we?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Kristen
Test

22859 Posts

Posted - 2010-10-18 : 13:42:11

I wonder if


WHERE Col LIKE ' %'
OR Col LIKE '% '

would be more efficient??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources