Why ltrim rtrim wont remove white space in select ltrim(rtrim('ABCDG '))?

SQL Server 2008
neehar gollapudiAsked:
Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
You might have 'special' characters hidden in there.

declare @vchar varchar(6) = ' ABCD '
declare @schar varchar(6) = 'ABCD' + char(10)

select @vchar,len(rtrim(ltrim(@vchar)))
select @schar,len(rtrim(ltrim(@schar)))

select patindex('%[^'+char(32)+'-'+char(126)+']%',@schar collate SQL_Latin1_General_CP850_Bin)  

Open in new window

Can explain the above in more detail....
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Neehar

I tired the same select ltrim(rtrim('ABCDG '))

it is working for me.

Do you have this data in this table. You can for other things like if there is a tab character or enter.

try this:

select ascii(right(columnX,1))
from (
    selct columnX from tableY where columnX like 'ABCDG%'
    ) d

Open in new window

It probably isn't a whitespace character that RTRIM deals with

once you know what the char number is, use REPLACE(columnX, CHAR(???), '')
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Mark WillsTopic AdvisorCommented:
@paul - snap - same thoughts :)
@mark - ditto - cheers!  (in between watching Ashes test)
Mark WillsTopic AdvisorCommented:
@paul, but it is lunch break now :)  (great minds think alike ? )
Just an observation - another good example of sql server needing to develop full regex functionality. POSIX [:space:] covers all white space i.e. space, line feed, carriage return, form feed, horizontal tab, and vertical tab) and could easily be us in a single replace statement. As it is, the following series of replaces should do the trick -
select replace(replace(replace(replace(replace(replace(string,char(9),''),char(10),''),char(11),''),char(12),''),char(13),''),char(32),'')
neehar gollapudiAuthor Commented:
thank you it was the carriage return.
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.