Solved

MSSQL QUERY - SELECT * WHERE FIELD IS NOT ALL WHITESPACE

Posted on 2011-09-27
8
233 Views
Last Modified: 2012-06-27
Hello,
I have a school that wants to use my TimeClock application. Some of the employees use the TimeClock and others do not. The ones that do not have a maximum length varchar full of spaces in the Login field. I need to be able to write a statement like this to see if an employee exists in the database....

SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND Login is not completely made of spaces

Anyone have an idea how to make the fourth line work?

Thanks so much.
0
Comment
Question by:tatton777
8 Comments
 
LVL 10

Expert Comment

by:dwe761
ID: 36714113
SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND len(ltrim( Login) = 0
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36714115
Sorry, I didn't see NOT.

Should be:

SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND len(ltrim( Login) > 0
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 167 total points
ID: 36714121
Missed a parenthesis

SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND len(ltrim( Login)) > 0
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 167 total points
ID: 36714833
Hi,

IF you are putting some value in textbox then there is no need for second condition.

- Bhavesh
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 36895344
As I suspect you have already discovered, the LEN() function alone is useless for this purpose as it ignores spaces trailing spaces.  So the following will produce the same result, namely 10:

SELECT LEN('John Smith'), LEN('John Smith                                        ')


A better approach is to take advantage of the fact that LEN() ignores spaces and use something like this:
SELECT *
FROM YourTable
WHERE LEN(YourColumn) <> DATALENGTH(YourColumn)    

Note: If YourColumn is nvarchar (and not varchar) you will have to modify this code.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36895346
Let's try that again to more fully comply with your names and requirements:

SELECT COUNT(*)
FROM Employees
WHERE  LEN(Login) = DATALENGTH(Login)  
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36895360
Just goes to show that there's always more to learn.
Excellent.
0
 
LVL 1

Author Closing Comment

by:tatton777
ID: 37053023
These guys were really helpful and complete in their answers. Once again Experts-Exchange community comes through with flying colors.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question