none
check for spaces in a sql server column

    Question

  • In a sql server 2008 r2 database column that is defined as varchar, how do you check to see if the field contains all spaces regardless of the length the field is?
    Saturday, October 06, 2012 8:19 PM

Answers

  • If LEN is 0 and DATALENGTH > 0. Demo:

    DECLARE @SpaceOnly varchar(32) = REPLICATE (CHAR(32), 20);
    SELECT LEN(@SpaceOnly), DATALENGTH(@SpaceOnly);
    -- 0	20


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Marked as answer by midnight_car Saturday, October 06, 2012 9:41 PM
    Saturday, October 06, 2012 8:36 PM
  • The len() function returns the length of the column in characters not counting trailing spaces.

    The datalength() function returns the length of the column in bytes, and all bytes are counted as equal.

    Thus, if the column is all spaces, len(col) is 0. To this Kalman added a condition on datalength to rule out rows where the column is the empty string. Whether you actually should add this condition depends on your requirements. Apparently Kalman interpreted "all spaces" as "at least one space".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by midnight_car Saturday, October 06, 2012 9:41 PM
    Saturday, October 06, 2012 9:13 PM

All replies

  • If LEN is 0 and DATALENGTH > 0. Demo:

    DECLARE @SpaceOnly varchar(32) = REPLICATE (CHAR(32), 20);
    SELECT LEN(@SpaceOnly), DATALENGTH(@SpaceOnly);
    -- 0	20


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    • Marked as answer by midnight_car Saturday, October 06, 2012 9:41 PM
    Saturday, October 06, 2012 8:36 PM
  • I do not understand your answer. Can you explain it to me?
    Saturday, October 06, 2012 8:55 PM
  • The len() function returns the length of the column in characters not counting trailing spaces.

    The datalength() function returns the length of the column in bytes, and all bytes are counted as equal.

    Thus, if the column is all spaces, len(col) is 0. To this Kalman added a condition on datalength to rule out rows where the column is the empty string. Whether you actually should add this condition depends on your requirements. Apparently Kalman interpreted "all spaces" as "at least one space".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by midnight_car Saturday, October 06, 2012 9:41 PM
    Saturday, October 06, 2012 9:13 PM
  • I would do it like this, assuming you wanted a BIT flag indicating the result

      case when len(vc)=0 and len(vc+'.')>1 then 1 else 0 end
    

    Consider this example:

    create table tbl (vc varchar(100));
    insert tbl values
      ('  '),(''),(null),('  abc'),('         '),('abc   ');
    select
      replace(vc, ' ', '*'),
      case when len(vc)=0 and len(vc+'.')>1 then 1 else 0 end
    from tbl
    order by vc;
    
    --- result
    
    COLUMN_0	COLUMN_1
    (null)	           0
    **	           1
                       0
    ********* 	   1
    **abc	    0
    abc***	    0

    Note: I replaced the spaces with *'s so it's easier to see.

    Saturday, October 06, 2012 9:47 PM