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

Is there any setting required, as sometimes ltrim and rtrim does'nt work.
searchsanjaysharmaAsked:
Who is Participating?
 
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
 
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
 
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.

All Courses

From novice to tech pro — start learning today.