i have a query which will return all the not null value for particular field.I have written query
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
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:
and ASCII(left(col1, 1)) != 0
answered Jan 21, 2011 at 12:03 AM
Matt Whitfield ♦♦
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!
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
answered Jan 21, 2011 at 09:31 AM
You do not need to trim the expression
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