Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

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

Is there any setting required, as sometimes ltrim and rtrim does'nt work.
0
searchsanjaysharma
Asked:
searchsanjaysharma
2 Solutions
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Can you define doesn't work?  specific errors
0
 
ValentinoVBI ConsultantCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
searchsanjaysharmaAuthor Commented:
tx
0
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now