question

user-806 avatar image
user-806 asked

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
sqlquery
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
can you explain a bit more - which values of col1 are still returning?
1 Like 1 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
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). ?
0 Likes 0 ·
user-806 avatar image user-806 commented ·
yes what ever you said is correct
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
+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?
2 Likes 2 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@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.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
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
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

user-806 avatar image user-806 commented ·
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.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
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.
0 Likes 0 ·
user-806 avatar image user-806 commented ·
yah i have checked for one character
0 Likes 0 ·
user-806 avatar image user-806 commented ·
hi mahnus, your query not giving desire results
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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) */
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Awesome answer sir, +1
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.