You could try this:

select * 
from yourtable
where ltrim(rtrim(yourcolumn)) = ''

The idea is that if trimming the value leaves you with an empty string, then all you had in the first place was whitespace.

You could also just do this:

select *
from yourtable
where yourcolumn like ' '

Note that I have tested the second query on SQL Server 2008 R2, and it doesn't work on 2014 as stated in the comments by @gunr2171

Finally, if you have tab, carriage return or line feed, the above will not work. What you can do is to first replace these values with a blank string, and then use the first query like so:

select * 
from yourtable
where ltrim(rtrim(replace(replace(replace(yourcolumn,char(9),''),char(10),''),char(13),''))) = ''

char(9) , char(10) and char(13) are used for tab, line feed and carriage return respectively.

I just had a problem with this particular situation, i needed to find and clean every field with white spaces, but i found 4 types of possibles white space in my database fields (Reference to ASCII code table ):

• Horizontal Tab(char(9))
• New Line(char(10))
• Vertical Tab(char(9))
• Space(char(32))

Maybe this query can help you.

SELECT @COLUMN
FROM @TABLE
WHERE @COLUMN like '%'+CHAR(9)+'%' or @COLUMN like '%'+CHAR(10)+'%'
or @COLUMN like '%'+CHAR(11)+'%' or @COLUMN like '%'+CHAR(32)+'%'

Based on shree.pat18's comment, here's a possible answer...

select * 
from yourtable
where ltrim(rtrim(yourcolumn)) = '' or yourcolumn is null

I think that should do the trick

I would try something like this

select *
from x
where len(ltrim(rtrim(coalesce(a,'')))) = 0

After examining my query it would appear redundant to use ltrim AND rtrim, just use one or the other.

the following will suffice

select *
from x
where len(ltrim(coalesce(a,''))) = 0

For full ASCII whitespace (tabs, line feeds, spaces, etc) and the flexibility to add other characters as needed, i.e CHAR(0):

WITH
  num256 AS (
     SELECT TOP 256 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) n
     FROM master.dbo.spt_values
  ),
  filter AS (
    SELECT CHAR(n-1) c FROM num256
    WHERE n NOT IN (0x09,0x0A,0x0B,0x0C,0x0D,0x20,0x85,0xA0)
  )
SELECT MyColumn
FROM MyTable
WHERE NOT EXISTS(
  SELECT 1
  FROM filter
  WHERE MyColumn LIKE '%'+c+'%'
)

Method string.IsNullOrWhiteSpace checks for 25 different unicode whitespace characters. For an equivalent check in SQL Server 2017+ we can use the TRIM function:

DECLARE @whitespaces nvarchar(50) =
    NCHAR(0x0020) + NCHAR(0x00A0) + NCHAR(0x1680) + NCHAR(0x1680) + NCHAR(0x2001)
    + NCHAR(0x2002) + NCHAR(0x2003) + NCHAR(0x2004) + NCHAR(0x2005) + NCHAR(0x2006)
    + NCHAR(0x2007) + NCHAR(0x2008) + NCHAR(0x2009) + NCHAR(0x200A) + NCHAR(0x202F)
    + NCHAR(0x205F) + NCHAR(0x3000) + NCHAR(0x2028) + NCHAR(0x2029) + NCHAR(0x0009)
    + NCHAR(0x000A) + NCHAR(0x000B) + NCHAR(0x000C) + NCHAR(0x000D) + NCHAR(0x0085);

SELECT *
FROM X
WHERE a IS NULL OR TRIM(@whitespaces FROM a) = ''

Since in this question a is not null, that part can be left out from the WHERE clause.

In lower versions of SQL Server, trimming specific characters requires a bit more effort, so in most cases, a regular LTRIM should be enough. But if you do need the exact C# equivalent, you can see how to trim specific characters here:

• SQL Server TRIM character
• How to trim string using predefined special characters in SQL Server?

Here is an example of an If statement that checks if a variable is null or whitespace (c# equivalent of IsNullOrWhitespace ).

declare @temp nvarchar(max) = '      ';

if(@temp is null
    or
   LEN(LTRIM(RTRIM(@temp))) = 0)
begin
    print 'it is empty or null'
end

You can make this inline by including the logic in a where clause.

select *
from someTable
where
    aValue is null
        or
    LEN(LTRIM(RTRIM(aValue))) = 0