Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ltrim and rtrim sometimes doensn't work on sql server.

Posted on 2013-01-20
5
Medium Priority
?
553 Views
Last Modified: 2013-01-21
Is there any setting required, as sometimes ltrim and rtrim does'nt work.
0
Comment
Question by:searchsanjaysharma
5 Comments
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38800006
Can you define doesn't work?  specific errors
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 1000 total points
ID: 38800050
LTRIM/RTRIM will only remove spaces. Strings can contain other invisible characters which are not spaces. You may experiencing that.

Use the ASCII function on the first (or last) character in the string to check if it's actually a space.

select ASCII(' ') (with a space in between the quotes) returns 32.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38800138
I confirm above. usually it's the "html" space (ascii code 160) which is the culprit.
if you do this:
ltrim(rtrim( replace(yourfield, chr(160), ' ') ))
it will trim those characters also

another gotcha is this: if the field is CHAR (and not VARCHAR), and update it using trim functions, it will will have the trailing spaces.
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 38803644
tx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38803784
Is there any setting required, as sometimes ltrim and rtrim does'nt work.
Yeah, it is quite complex, but it involves reading the definition of LTRIM() and RTRIM() in SQL Server's BOL and (this is the important part) understanding how it works or "does'nt work".
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question