T-sql 2012 all spaces

1

In a t-sql 2012 script, I want to check if the entire value in a varchar(50) column called testfield = all spaces. How would I do that type of a t-sql check?

2

Example:
declare
@var varchar(50)
set @var= ' '
select DATALENGTH(@var) 'datalength' --It will count number bytes required
select LEN(@var) 'len'--It trims trailing blanks

datalength

13

len

0

So you can check like where len(testfiled) = 0

3

This field is actually like a city value. There may be spaces between words and I do not want to select those fields. Example:
New York City has a space between the words and I do not want to select these fields. Thus would you tell me how to not select that field where there is a couple of spaces in the entire field?

4 
city LIKE ' %'
AND city NOT LIKE '%[^ ]%'
5

where len(City) = len(Replace(City, ' ', ''))

1 Like
6

Needs DATALENGTH instead of LEN I think? (if it is important to "catch" trailing spaces)

Seems like quite a lot of CPU to do REPLACE and then LEN, but I haven't benchmarked it ... can't imagine that anything is going to be particularly "fast"

7 
Declare @myVariable varchar(50) = space(50)
      , @myData varchar(50) = 'New York City';

Select @myVariable + '|';
Select len(@myVariable), datalength(@myVariable);
Select len(@myData), datalength(@myData);

Select Case When len(@myVariable) = 0 Then 'All Spaces' End;
Select Case When len(@myData) <> 0 Then 'Not All Spaces' End;

This is what @mmkrishna1919 was trying to show you - if the column has all spaces then checking the length of the column will give you a value of 0 for the length. If it does not have all spaces you will get a value greater than 0...

So your check is very simple...

Select *
From YourTable
Where len(YourColumn) <> 0

But - you don't need LEN - you can do the following and it will work...and in fact perform much better because you are not using a function on the column which could prevent an index from being used.

Select Case When @myVariable = '' Then 'All Spaces' End;

So now your check to find the 'blank' columns is:

Select *
From YourTable
Where YourColumn = ''

To find all non-blanks just use YourColumn > '' and you can eliminate the rows where that column is blank or completely filled with spaces.

1 Like
8

Will also include columns that are empty (but not NULL). I have no idea if that distinction is important?

I think?? this will find any column with one, or multiple, spaces (but no other characters)

Select *
From YourTable
Where YourColumn = ' ' -- << Single space
9

Unfortunately - that doesn't work and will be true for a column with 0 or more spaces. If you need to identify that a column has a specific number of spaces (either leading or trailing) - then you have to use the datalength function.