Post reply

t-sql 2012 spaces

  • March 17, 2017 at 8:08 am

    #319678

    In a t-sql 2012 script, I want to check if the entire value in a varchar(50) column called testfield = all spaces. How would I do that type of a t-sql check?

  • March 17, 2017 at 9:07 am

    #1933767

    A combination of DataLength and testing for value = '' is probably the best bet.

    Consider this:
    DECLARE @t TABLE
    (
      Label VARCHAR(20),
      TestString VARCHAR(50)
    );

    INSERT INTO @t
    VALUES
    ('Empty String', ''),
    ('One Space', ' '),
    ('Fifty Spaces', REPLICATE(' ', 50)),
    ('LF', CHAR(10)),
    ('CR', CHAR(13)),
    ('CRLF', CHAR(13) + CHAR(10)),
    ('NBSP', CHAR(160));

    SELECT Label,
      TestString,
      DATALENGTH(TestString) AS Length,
      CASE
       WHEN TestString = '' THEN
        'Empty'
       ELSE
        'Data'
      END AS StringHasData
    FROM @t;

    As you will see, the DataLength accurately reports the number of spaces (or other Characters), even though the string you're looking for matches a test for an empty string.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply