CODEWITHSUNDEEP
c#sql-servert-sqlwhitespace
MoXplod
MoXplod

Reputation: 3852

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.

Upvotes: 13

Views: 80691

Answers (7)

PajLe
PajLe

Reputation: 924

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:

Upvotes: 1

sami.almasagedi
sami.almasagedi

Reputation: 163

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

Upvotes: 12

Anon
Anon

Reputation: 10908

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

Upvotes: 0

landers
landers

Reputation: 105

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

Upvotes: 4

shree.pat18
shree.pat18

Reputation: 21757

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.

Upvotes: 20

user3703582
user3703582

Reputation:

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

Upvotes: 0

gunr2171
gunr2171

Reputation: 17510

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

Upvotes: -1

Related Questions