x

Ltrim Rtrim not working

HI,

i have a query which will return all the not null value for particular field.I have written query

 select col1 from test where col1 is not null and len(ltrim(rtrim(col1)))>0 and ltrim(rtrim(col1))!='' 
but this query retun one record which is null at visible point of view. when I check its length it showing 17(after using ltrim & rtrim also).So i check ascii value for one substring & it is 0.

So kindly let me know how to omit this record from my selection in sql 2005.

Thanks in advance Deba

more ▼

asked Jan 20 '11 at 09:23 PM in Default

user-806 gravatar image

user-806
181 16 18 21

can you explain a bit more - which values of col1 are still returning?
Jan 20 '11 at 11:37 PM Kev Riley ♦♦
Please correct me if this is wrong but you are saying that the above query returns a row that is 17 characters in length but none of it is visible in the results grid and the 1st character of the 17 is ASCII(0). ?
Jan 21 '11 at 12:20 AM Fatherjack ♦♦
yes what ever you said is correct
Jan 21 '11 at 12:35 AM user-806
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Jan 21 '11 at 12:03 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

+1 for this. However, I wonder about two WHERE-conditions

len(ltrim(rtrim(col1)))>0 

AND

ltrim(rtrim(col1))!=''

Aren't they checking the same thing? If SOME_STRING='' it would imply LEN(SOME_STRING)=0

Also if LEN(SOME_STRING)=0 AND SOME_STRING IS NOT NULL, would imply SOME_STRING=''

So I find LEN(SOME_STRING)>0 redundant. Am I right?
Jan 21 '11 at 02:04 AM Magnus Ahlkvist
@Magnus - I think you are, yes. I just added the last bit for checking that it wasn't a C-style null terminator in the first character position, and didn't really think about the other predicates.
Jan 21 '11 at 03:03 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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)
*/
more ▼

answered Jan 21 '11 at 09:31 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

Awesome answer sir, +1
Jan 21 '11 at 11:23 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Jan 20 '11 at 11:14 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

I am not able to under stand what you want to say.can you explain or give me syntax to omit those type of records.
Jan 20 '11 at 11:34 PM user-806

You should be OK doing:

select col1 
from test 
where col1 is not null 
and ltrim(col1)<>''
You say "So i check ascii value for one substring & it is 0.". What do you mean when you say that you check ascii value for one substring? Ascii values are for characters, not for whole strings.
Jan 20 '11 at 11:39 PM Magnus Ahlkvist
yah i have checked for one character
Jan 21 '11 at 12:41 AM user-806

hi mahnus,

your query not giving desire results
Jan 21 '11 at 12:53 AM user-806
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x673
x362

asked: Jan 20 '11 at 09:23 PM

Seen: 3728 times

Last Updated: Jan 20 '11 at 10:10 PM