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?
set @var= ' '
select DATALENGTH(@var) 'datalength' --It will count number bytes required
select LEN(@var) 'len'--It trims trailing blanks
So you can check like where len(testfiled) = 0
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?
city LIKE ' %' AND city NOT LIKE '%[^ ]%'
where len(City) = len(Replace(City, ' ', ''))
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"
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.
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
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.