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

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.

Vaibhav

try this:

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

Select allOpen 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(???), '')

@paul - snap - same thoughts :)

@mark - ditto - cheers!  (in between watching Ashes test)

@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),'')

thank you it was the carriage return.