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.

You also want to consider columns with `NULL` values as being whitespace? — hatchet - done with SOverflow, Jun 10 '14 at 16:52
@TrippKinetics I have sql server 2008 r2. i am getting - 'ISBLANK' is not a recognized built-in function name. — MoXplod, Jun 10 '14 at 16:55
"Whitespace" defines a broad set: spaces (at least 20 different kinds), non-breaking spaces, line separators, tabulations, paragraph separators, etc. Can you narrow the scope, or do you need to design defensively because this is a sanitizing pass? — Anon, Jun 10 '14 at 17:12
FYI: Here is Microsoft's definition of whitespace http://msdn.microsoft.com/en-us/library/system.char.iswhitespace.aspx — UnhandledExcepSean, Jun 10 '14 at 17:15
@Anon But you changed words. IsNullOrWhiteSpace is a defined set. "Whitespace" in my mind is general. — paparazzo, Jun 10 '14 at 19:30

shree.pat18 · Accepted Answer · 2014-06-10T17:17:57.230

19

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.

edited Jun 10 '14 at 17:17

answered Jun 10 '14 at 16:56

shree.pat18

21,449
3
43
63

Doesn't handle NULL values. – Stan Jun 10 '14 at 16:58
2

"Column a is NOT NULL" - OP said it's not going to be, hence not handling nulls. – shree.pat18 Jun 10 '14 at 16:59
Your second example would only work if the value is a single space. – gunr2171 Jun 10 '14 at 17:02
That's what I thought initially as well, but it does for multiple spaces too. Demo: http://rextester.com/ACUUL22481 – shree.pat18 Jun 10 '14 at 17:04
Not working for me with 2014 server. `declare @temp nvarchar(max) = ' '; if(@temp like ' ') print 'yes'` – gunr2171 Jun 10 '14 at 17:07
@gunr2171 Fair enough, I'm testing on 2008 R2 and have no access to 2014. – shree.pat18 Jun 10 '14 at 17:08
selecting this as answer - the first statement works well enough for me. – MoXplod Jun 10 '14 at 20:23
The second example would work with wildcards I think, where yourcolumn like '% %' – Nhan Jul 07 '16 at 19:38

score 11 · Answer 2 · answered Feb 01 '17 at 15:52

11

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

answered Feb 01 '17 at 15:52

sami.almasagedi

153
1
5

This to me is the simplest solution – Karlomanio Nov 05 '18 at 19:57
Except this would select something with values like "hello world" where the whitespace in the middle is one of those 4 characters. OP asked for rows where it has only whitespace. – Brian Davis Sep 08 '21 at 15:07

score 4 · Answer 3 · edited Jun 10 '14 at 17:27

4

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

edited Jun 10 '14 at 17:27

La-comadreja

5,627
11
36
64

answered Jun 10 '14 at 17:08

landers

105
9

Not sure if this is correct but it would be my first try. +1 – paparazzo Jun 10 '14 at 18:14

score 1 · Answer 4 · answered Mar 05 '23 at 13:48

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:

score 0 · Answer 5 · answered Jun 10 '14 at 16:59

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

Anon · Answer 6 · 2014-06-10T17:37:50.457

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

score -1 · Answer 7 · answered Jun 10 '14 at 16:56

-1

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

answered Jun 10 '14 at 16:56

gunr2171

16,104
25
61
88

Having both LTRIM and RTRIM is redundant. If there is any character in the value no matter how many trims you throw at it it will still have that character. – Eric Scherrer Jun 10 '14 at 17:39

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

7 Answers7