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, 2011 at 09:23 PM in Default

avatar image

user-806
181 20 23 27

can you explain a bit more - which values of col1 are still returning?

Jan 20, 2011 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, 2011 at 12:20 AM Fatherjack ♦♦

yes what ever you said is correct

Jan 21, 2011 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, 2011 at 12:03 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

  • 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, 2011 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, 2011 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, 2011 at 09:31 AM

avatar image

Phil Factor
4.2k 8 23 20

Awesome answer sir, +1

Jan 21, 2011 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, 2011 at 11:14 PM

avatar image

Håkan Winther
16.5k 36 45 57

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, 2011 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, 2011 at 11:39 PM Magnus Ahlkvist

yah i have checked for one character

Jan 21, 2011 at 12:41 AM user-806

hi mahnus,

your query not giving desire results

Jan 21, 2011 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.

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:

x993
x426

asked: Jan 20, 2011 at 09:23 PM

Seen: 5191 times

Last Updated: Jan 20, 2011 at 10:10 PM

Copyright 2016 Redgate Software. Privacy Policy