Ltrim Rtrim not working

Ideally, you shouldn't be doing that sort of thing in a query. If you have a string with a null terminator as it's first character, that should have been sorted out before you inserted the record.

That having been said, you can just alter your query to avoid returning that row:

select col1 
from test 
where col1 is not null 
and len(ltrim(rtrim(col1)))>0 
and ltrim(rtrim(col1))!=''
and ASCII(left(col1, 1)) != 0

Matt is right to warn about letting CHAR(0) anywhere near SQL Server. SQL Server doesn't really cope with a CHAR(0) very well Most string functions can't 'see' it, even though it won't terminate the string at that point. I suspect that the Sybase programmers were C programmers and Microsoft haven't got around to pulling out the bugs that were put in then. However, LTRIM and RTRIM work as you would expect, which is to take of just the leading or trailing spaces. This means that if you have a CHAR(0) in amongst leading or trailing spaces, it will look as if LTRIM or RTIM aren't working!

 SELECT  CHARINDEX(CHAR(0),'this'+CHAR(0)+' is a legitimate string')
 SELECT  CHARINDEX(CHAR(1),'this'+CHAR(1)+' is a legitimate string')
 SELECT  CHARINDEX(CHAR(3),'this'+CHAR(3)+' is a legitimate string')
 SELECT  CHARINDEX(CHAR(4),'this'+CHAR(4)+' is a legitimate string')
 /*
-----------
0
(1 row(s) affected)
-----------
5
(1 row(s) affected)
-----------
5
(1 row(s) affected)
-----------
5
(1 row(s) affected)
SELECT REPLACE('This'+CHAR(0)+'odd',CHAR(0),' is ')     
SELECT REPLACE('This'+CHAR(1)+'odd',CHAR(1),' is ')     
--------------------------------------
This odd
(1 row(s) affected)

--------------------------------------
This is odd
(1 row(s) affected)
SELECT LTRIM(RTRIM('   a string with a queer character in it      '+CHAR(0)+'                       ')+'!')  

 -----------------------------------------------------------------------
 a string with a queer character in it       !

I hope that this little test-rig and the 'brute-force' solution in the WHERE clause helps with the problem as I'm not clear if you want to truncate your strings at the CHAR(0) as in C, or to ignore the CHAR(0)s. I've ignored them

SELECT  String
FROM    (SELECT CHAR(0) + 'a string' AS string
         UNION ALL
         SELECT '         '
         UNION ALL
         SELECT NULL
         UNION ALL
         SELECT ''
         UNION ALL
         SELECT '    another string  '
         UNION ALL
         SELECT CHAR(13) + CHAR(10) + ' ' + CHAR(08)) TestData
WHERE   COALESCE(string, '') LIKE '%[1-9A-Za-z]%'  

/* returns
    ----------------------------------------------
    String
    --------------------
     a string
        another string  

    (2 row(s) affected)
 */    

You do not need to trim the expression

Books online:

Returns the number of characters of the specified string expression, excluding trailing blanks.

Try this code, it's basically the same code as yours and tell us the result. When I use the code, it doesn't return the NULL values

DECLARE @t AS TABLE
(
    a VARCHAR(15)
)
INSERT INTO @t
         ([a])
VALUES
         ('asd'),
         (NULL),
         ('aas   ');

         SELECT a
         FROM @t AS T 
         WHERE ltrim(rtrim(a))!='' 
         and len(ltrim(rtrim(a)))>0  
         AND a is not null