Sql server - how to find rows with spaces in a column

Question

I want to do something like

select * from X where string.IsNullOrWhiteSpace(a) 

Column a NOT NULL

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

In addition, I would prefer not to use clr .

Answer 1

You can try the following:

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

The idea is that if trimming the value leaves you an empty string, then everything that you had in the first place was a space.

You can also just do this:

 select * from yourtable where yourcolumn like ' ' 

Please note that I checked the second query on SQL Server 2008 R2, and it does not work in 2014, as pointed out in comments by @ gunr2171

Finally, if you have a tab, carriage return, or line feed, the above will not work. What you can do is first replace these values ​​with an empty string, and then use the first query as follows:

 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 to display tabs, lines and carriage returns, respectively.

Answer 2

Based on shree.pat18 comment, here is a possible answer ...

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

I think it was supposed to do the trick

Answer 3

I would try something like this

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

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

it will be enough

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

Answer 4

For full ASCII space (tabs, lines, 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+'%' ) 

Answer 5

I just had a problem with this particular situation, I needed to find and clear each field with spaces, but I found 4 types of possible spaces in my database fields (Link to ASCII Code Table ):

• Horizontal tab (char (9))
• New line (char (10))
• Vertical tab (char (9))
• Space (char (32))

Perhaps this query may 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)+'%' 

Answer 6

Here is an example of an If statement that checks if a variable is null or whitespace (C # is equivalent to 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 do this inline by including logic in the where clause.

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