Solved

MSSQL QUERY - SELECT * WHERE FIELD IS NOT ALL WHITESPACE

Posted on 2011-09-27
8
229 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now