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

Question

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 clr functions.

Answer 1

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.

Answer 2

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)+'%'