Search code examples
c#sql-servert-sqlwhitespace

sql server - how do i find rows with whitespace in a column

I want to do something like 

select * from X where string.IsNullOrWhiteSpace(a)

Column a is NOT NULL

So what would be the equivalent of C# string.IsNullOrWhiteSpace in T-SQL to get all rows where column a has only whitespace (combination of multiple spaces or tabs)?

Also, I would rather avoid using functions.

Solution

  • 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.