Strange behavior when you compare char/varchar field with empty string or string with spaces only

• Shriji

  SSCommitted

  Points: 1881

  More actions

  September 10, 2010 at 7:15 am

  #141966

  Hi

  I am dealing with a strange behavior with following scnario. I just wonder how does SQL engine treats comparison with spaces. A given field has some records with spaces only. Number of spaces vary in each records. However when we search for records let say with only 1 space, it returns all the rows with spaces totally ignoring the fact that condition specified only one space. I tried ANSI_PADDING ON and OFF but same results.

  Your views are appreciated.

  --SET ANSI_PADDING ON;

  DROP TABLE #T

  Create Table #T

  (Id Int identity

  , Char1 Char(15),

  VChar1 varchar(10))

  INSERT INTO #T (Char1,VChar1)

  SELECT Null,NULL

  UNION ALL SELECT Null,''

  UNION ALL SELECT '',Null

  UNION ALL SELECT ' ',' '

  UNION ALL SELECT ' ',' '

  UNION ALL SELECT ' ',' '

  SELECT * FROM #t

  SELECT *,'>'+ Char1+ '<' , '>' + VChar1 + '<', DATALENGTH (VChar1)

  FROM #T

  SELECT * FROM #T WHERE VChar1 =' '

  SELECT * FROM #T WHERE Char1 =''

• sturner

  SSC-Insane

  Points: 22411

  More actions

  September 10, 2010 at 8:20 am

  #1219199

  char data type is padded with spaces so a char value of 1 space is equal to another with 2 or more spaces.

  The probability of survival is inversely proportional to the angle of arrival.